Skip to content

Instantly share code, notes, and snippets.

tdmitch

Block or report user

Report or block tdmitch

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View purge_ssis.sql
/*
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.
View merge3.sql
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
View merge2.sql
/*************************************************************
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
View selectdelta.sql
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
View updates.sql
UPDATE dbo.Emp
SET MiddleName = 'Muriel'
WHERE FirstName = 'Chandler'
AND LastName = 'Bing'
UPDATE dbo.Emp
SET LastName = 'Bing'
WHERE FirstName = 'Monica'
AND LastName = 'Geller'
View selectchanges.sql
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
View setctversion.sql
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()
View merge.sql
MERGE etl.Change_Tracking_Version AS target
USING (SELECT 'Emp' [Table_Name]
, CHANGE_TRACKING_CURRENT_VERSION() [Version_ID] — Get the change tracking version ID
) AS source
ON target.Table_Name = source.Table_Name
WHEN MATCHED — Exists? Then update
THEN UPDATE
SET target.Change_Tracking_Version = source.Version_ID
View create_emp.sql
CREATE TABLE Emp
(
ID INT PRIMARY KEY IDENTITY(1, 1)
, FirstName VARCHAR(100)
, MiddleName VARCHAR(100)
, LastName VARCHAR(100)
)
ALTER TABLE dbo.Emp ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
View gist:e2edfd4c452a22896bc9a40426628686
CREATE TABLE Emp
(
ID INT PRIMARY KEY IDENTITY(1, 1)
, FirstName VARCHAR(100)
, MiddleName VARCHAR(100)
, LastName VARCHAR(100)
)
ALTER TABLE dbo.Emp ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
You can’t perform that action at this time.