Use MERGE and OUTPUT in TSQL to capture the old and new values when importing data.

MERGE_and_OUTPUT.sql

--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;