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.
UPDATE
`project.dataset.table1` t1
SET
t1.target_column = t2.source_column
FROM
`project.dataset.table2` t2
WHERE
t1.join_key = t2.join_key;
46
T-SQL
Datenbank
DB-Funktion erstellen
-- Scalar-value
CREATE FUNCTION whichContinent
(@country nvarchar(15))
RETURNS varchar(30)
AS
BEGIN
DECLARE @returnValue varchar(30)
SELECT @returnValue = CASE @country
WHEN 'ARGENTINA' THEN 'SOUTH AMERICA'
WHEN 'BELGIUM' THEN 'EUROPE'
WHEN 'BRAZIL' THEN 'SOUTH AMERICA'
WHEN 'CANADA' THEN 'NORTH AMERICA'
WHEN 'DENMARK' THEN 'EUROPE'
WHEN 'FINLAND' THEN 'EUROPE'
WHEN 'FRANCE' THEN 'EUROPE'
ELSE 'UNKNOWN'
END
RETURN @returnValue
END
47
T-SQL
Datenbank
Temporäre DB-Tabelle
-- 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
54
T-SQL
Datenbank
Stored Function aufrufen
SELECT schema.functionname(para1, para2)
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
