Created
November 24, 2018 00:18
-
-
Save paschott/1757bb2e150cee707fe69a5478795687 to your computer and use it in GitHub Desktop.
Use MERGE and OUTPUT in TSQL to capture the old and new values when importing data.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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