Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Use MERGE and OUTPUT in TSQL to capture the old and new values when importing data.
--Create test table to represent data we want to import.
CREATE TABLE #ProductsToImport (
OriginalSystemID INT,
ProductName NVARCHAR(50),
ProductNumber NVARCHAR(25),
SafetyStockLevel SMALLINT,
ReorderPoint SMALLINT,
StandardCost MONEY,
ListPrice MONEY,
DaysToManufacture INT,
SellStartDate DATETIME
);
INSERT #ProductsToImport
(
OriginalSystemID,
ProductName,
ProductNumber,
SafetyStockLevel,
ReorderPoint,
StandardCost,
ListPrice,
DaysToManufacture,
SellStartDate
)
VALUES
(-1, N'My Test Product 1', N'TS-1111', 10, 50, 0.00, 0.00, 1, GETUTCDATE()),
(-2, N'My Test Product 2', N'TS-2222', 10, 50, 0.00, 0.00, 1, GETUTCDATE()),
(-3, N'My Test Product 3', N'TS-3333', 10, 50, 0.00, 0.00, 1, GETUTCDATE()),
(-4, N'My Test Product 4', N'TS-4444', 10, 50, 0.00, 0.00, 1, GETUTCDATE()),
(-5, N'My Test Product 5', N'TS-5555', 10, 50, 0.00, 0.00, 1, GETUTCDATE());
--Create table to track the mapping of the original ID to the new ProductID
CREATE TABLE #ProductMapping (
OriginalSystemID INT,
ProductID INT
);
--Import into the Products table, storing the Old/New IDs in the #ProductMapping table.
;
MERGE INTO Production.Product AS p
USING (
SELECT pti.OriginalSystemID, --Note that this column will not be inserted, but we can fetch the value later.
pti.ProductName,
pti.ProductNumber,
pti.SafetyStockLevel,
pti.ReorderPoint,
pti.StandardCost,
pti.ListPrice,
pti.DaysToManufacture,
pti.SellStartDate
FROM #ProductsToImport AS pti
) AS src
ON 1 = 0 --want to make sure we never get a match in this case
WHEN NOT MATCHED
THEN INSERT(Name, ProductNumber, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, DaysToManufacture, SellStartDate )
--use the columns in the above "src" query
VALUES (
src.ProductName,
src.ProductNumber,
src.SafetyStockLevel,
src.ReorderPoint,
src.StandardCost,
src.ListPrice,
src.DaysToManufacture,
src.SellStartDate
)
--Now use the OUTPUT statement to get the OriginalID from the "src" query
--and the newly created Identity value from the Product table
--Insert those into the #ProductMapping table
OUTPUT src.OriginalSystemID, inserted.ProductID
INTO #ProductMapping (OriginalSystemID, ProductID);
--Verify results in mapping table
SELECT pm.OriginalSystemID, pm.ProductID
FROM #ProductMapping AS pm;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment