You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
-- lokale Temporäre Tabelle (Livetime nur innerhalb von STORED PROCEDURES)
SELECT * INTO #tempdaten FROM myTable
-- ODER globale temporäre Tabellen (Livetime auch außerhalb von STORED PROCEDURES)
SELECT * INTO ##tempdaten FROM myTable
48
T-SQL
Datenbank
Tabelleinhalt DB übergreifend kopieren
INSERT INTO DestinationDB.dbo.Table1 SELECT * FROM SourceDB.dbo.Table1
-- Alternative
-- bereits vorhandene Tabelle in der Ziel-DB löschen
DROP TABLE DB_DESTINATION.dbo.TableCopy
SELECT * INTO DB_DESTINATION.dbo.TableCopy FROM DB_SOURCE.dbo.TableOrginal
49
T-SQL
Datenbank
Die letzte eingefügte ID eines INSERT ermitteln
-- Nach dem INSERT folgenden SELECT durchführen
-- Achtung bei Verwendung mit TRIGGERN, diese Funktion liefert auch eingefügte Werte von TRIGGERN
-- (unabhängig der Tabelle)
SELECT @@Identity AS NewID
-- Möchte man alle IDs (oder erzeugten Werte speichen, kann man nachfolgende Variante wählen):
IF OBJECT_ID(CONCAT('tempdb..','#Test01')) IS NOT NULL DROP TABLE #Test01;
CREATE TABLE #Test01 ( id INT IDENTITY(1,1) PRIMARY KEY, value1 NVARCHAR(50) );
IF OBJECT_ID(CONCAT('tempdb..','##saveOUTPUT')) IS NOT NULL DROP TABLE ##saveOUTPUT;
CREATE TABLE ##saveOUTPUT ( inserted_id INT, some_text NVARCHAR(50) );
INSERT INTO #Test01 (value1)
OUTPUT INSERTED.id, 'Another Text' INTO ##saveOUTPUT
VALUES ('Hello World!')
INSERT INTO #Test01 (value1)
OUTPUT INSERTED.id, 'Another Text' INTO ##saveOUTPUT
VALUES ('Hello World!')
SELECT * FROM ##saveOUTPUT
51
T-SQL
Datenbank
Alle angelegten Tabellen anzeigen
SELECT
T.TABLE_CATALOG
,T.TABLE_SCHEMA
,T.TABLE_NAME
,T.TABLE_TYPE
FROM
INFORMATION_SCHEMA.TABLES T
SELECT GETDATE();
INSERT INTO table (datetime) VALUES (GETDATE());
57
T-SQL
Datenbank
SQL-Cursor
CREATE #myTempTable
(
ID Int IDENTITY
,tempField1 Int
,tempField2 VarChar(30)
)
DECLARE @field1 AS Int
DECLARE @field2 AS VarChar(30)
DECLARE myCursor CURSOR FOR SELECT myTableField1, myTableField2 FROM myTable
OPEN myCursor
FETCH NEXT FROM myCursor INTO @field1, @field2;
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @field1 -- Prints value from column myTable.myTableField1
PRINT @field2 -- Prints value from column myTable.myTableField2
INSERT INTO #myTempTable (tempField1,tempField2) VALUES (@field1, @field2)
-- do something else...
FETCH NEXT FROM myCursor INTO @field1, @field2; -- next row
END
CLOSE myCursor
DEALLOCATE myCursor
DROP #myTempTable
59
T-SQL
Datenbank
Fortlaufende Nummer innerhalb eines ResultSets
-- Beispiel:
-- http://msdn.microsoft.com/de-de/library/ms186734.aspx
SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode FROM Sales.vSalesPerson
60
T-SQL
Datenbank
Nach Inhalt in Functions/Stored Procedures suchen
-- Durchsucht alle Functions, Stored Procedures, Views nach dem enthaltenen Begriff dbo.table1:
DECLARE @suchbegriff VarChar(50) = '%dbo.table1%'
SELECT
*
FROM
(
SELECT
ROUTINE_SCHEMA AS [Schema]
, ROUTINE_NAME AS [Objectname]
, ROUTINE_DEFINITION AS [Objectdefinition]
, ROUTINE_TYPE AS [Objecttype]
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE @suchbegriff
AND (ROUTINE_TYPE='PROCEDURE' OR ROUTINE_TYPE='FUNCTION')
UNION ALL
SELECT
TABLE_SCHEMA AS [Schema]
, TABLE_NAME AS [Objectname]
, VIEW_DEFINITION AS [Objectdefinition]
, 'VIEW' AS [Objecttype]
FROM
INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE @suchbegriff
) subQuery1
ORDER BY subQuery1.[Objectname]
-- gefunden bei: http://www.mssqltips.com/tip.asp?tip=1419
-- erweitert um View-Definition
