tdmitch

CREATE TABLE #idtest
(
  theID INT IDENTITY(1,1)
  , theValue VARCHAR(20) NULL
)

INSERT #idtest
VALUES ('Plane')
  , ('Train')
  , ('Automobile')

tdmitch

-- Show cars with a type other than 'Coupe' or NULL. Corrected to account for NULLs.
SELECT *
FROM #Automobiles
WHERE AutoType NOT IN ('Coupe')
AND AutoType IS NOT NULL 

tdmitch

IF (OBJECT_ID('tempdb..#Automobiles') IS NOT NULL)
	DROP TABLE #Automobiles


CREATE TABLE #Automobiles
(
	AutoMake VARCHAR(50) NULL 
	, AutoModel VARCHAR(50) NULL 
	, AutoType VARCHAR(50) NULL 
)

tdmitch

SSMSTips
SSMS Tips		      -- One space
SSMS  Tips		      -- Two spaces
SSMS	Tips	              -- That's a tab
SSMS
Tips			      -- Carriage return
SSMS Quick Tips	              -- An extra word
SSMS 11 Tips	              -- Numbers

tdmitch

/*
	Script name: Purge SSIS Catalog log tables
	Author: Tim Mitchell (www.TimMitchell.net)
	Date: 12/19/2018

	Purpose: This script will remove most of the operational information from the SSIS catalog. The 
				internal.operations and internal.executions tables, as well as their dependencies, 
				will be purged of all data with an operation created_time value older than the number
				of days specified in the RETENTION_WINDOW setting of the SSIS catalog.
				

tdmitch

MERGE etl.Change_Tracking_Version AS target

USING (
SELECT 'Emp' [Table_Name]
, @EndVersionID [Version_ID] -- Get the change tracking version ID
) AS source
ON target.Table_Name = source.Table_Name

-- Update existing value
WHEN MATCHED THEN UPDATE

tdmitch

/*************************************************************
The MERGE statement updates the DimEmp table
*************************************************************/
MERGE [dw].[DimEmp] AS target

USING (
-- Here is the original source query, now wrapped in a MERGE statement
SELECT ct.ID
, e.FirstName
, e.MiddleName

tdmitch

DECLARE @StartVersionID BIGINT, @EndVersionID BIGINT

-- Set the starting version ID
SET @StartVersionID = (
SELECT Change_Tracking_Version
FROM etl.Change_Tracking_Version
WHERE Table_Name = 'Emp')

-- Set the ending version ID
SET @EndVersionID = CHANGE_TRACKING_CURRENT_VERSION() -- Set the ending version ID

tdmitch

UPDATE dbo.Emp
SET MiddleName = 'Muriel'
WHERE FirstName = 'Chandler'
AND LastName = 'Bing'

UPDATE dbo.Emp
SET LastName = 'Bing'
WHERE FirstName = 'Monica'
AND LastName = 'Geller'

tdmitch

SELECT ct.ID
, e.FirstName
, e.MiddleName
, e.LastName
, ct.SYS_CHANGE_OPERATION [Operation]
FROM CHANGETABLE(CHANGES dbo.Emp, @StartVersionID) ct
LEFT JOIN dbo.Emp e
ON e.ID = ct.ID
WHERE (SELECT MAX(v) FROM (VALUES(ct.SYS_CHANGE_VERSION), (ct.SYS_CHANGE_CREATION_VERSION)) AS VALUE(v)) <= @EndVersionID