tdmitch

USE SSISDB;
GO

DECLARE @execution_id BIGINT;
EXEC [SSISDB].[catalog].[create_execution]
	@package_name = N'DEMO SFTP List.dtsx'
	, @execution_id = @execution_id OUTPUT
	, @folder_name = N'SSIS Parameters'
	, @project_name = N'SSIS Parameters'
	, @use32bitruntime = False

tdmitch

BEGIN TRAN

INSERT #idtest
VALUES ('Hop')
  , ('Skip')
  , ('Jump')

ROLLBACK TRAN

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

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'