Introduction
Today I want to share a script with you that I used to copy some database rows that use unique identifiers (or guids).
I had related rows that I needed to copy (compare with OrderHeader / OrderDetail where OrderDetail contains the OrderHeaderGuid)
Problem
When you want to copy related records that are identified with unique identifiers, you have a dependency on the unique identifier values that are used in other tables as foreign keys.
Especially when you want to copy multiple rows (having multiple different unique identifiers) creating new predictable guids from the existing ones can be useful.
In the example below I explain how this can be done using a MD5 hash of the source guid.
The example uses the AdventureWorks database.
Solution
--\
---) Script: Generate new predictable GUIDs from existing ones - demo
---) Author: Hans Michiels
---) Website: www.hansmichiels.com
--/
--\
---) 1. Prepare for the demo
--/
USE [tempdb]
GO
IF OBJECT_ID('[dbo].[SalesOrderHeader]', 'U') IS NOT NULL
DROP TABLE [dbo].[SalesOrderHeader];
IF OBJECT_ID('[dbo].[SalesOrderDetail]', 'U') IS NOT NULL
DROP TABLE [dbo].[SalesOrderDetail];
GO
SELECT NEWID() AS [SalesOrderGUID], h.*
INTO [dbo].[SalesOrderHeader]
FROM [AdventureWorks].[Sales].[SalesOrderHeader] h
SELECT h.SalesOrderGUID, NEWID() AS [SalesOrderDetailGUID], d.*
INTO [dbo].[SalesOrderDetail]
FROM [AdventureWorks].[Sales].[SalesOrderDetail] d
JOIN [dbo].[SalesOrderHeader] h ON h.SalesOrderID = d.SalesOrderID
ALTER TABLE [tempdb].[dbo].[SalesOrderHeader] DROP COLUMN SalesOrderID;
ALTER TABLE [tempdb].[dbo].[SalesOrderDetail] DROP COLUMN SalesOrderID;
ALTER TABLE [tempdb].[dbo].[SalesOrderDetail] DROP COLUMN SalesOrderDetailID;
GO
--\
---) 2. Demonstrate how it works given the example that you would
---) want to copy an existing SalesOrder and its detail rows to
---) a new one, without having the dependency to insert the
---) header first, then retrieve the value for [SalesOrderGUID]
---) for the row, and then use it to insert the detail rows.
---) Using a new predictable unique identifier this lookup is
---) not needed.
--/
-- First we declare a value to use for 'Salt'. This makes sure that we could
-- copy the same source rows multiple times to new rows, because the generated
-- guids will be different every time the script runs.
-- We keep it simple and just use a timestamp as a salt value.
DECLARE @NewGuidSalt VARCHAR(30) = CONVERT(VARCHAR, GETDATE(), 120)
PRINT @NewGuidSalt
-- Again I keep the example very simple, hypothetical almost because the action
-- below is not likely for a real life example: we select 3 salesorders from a specific CustomerID and copy them to a different CustomerID.
-- However it explains the concept.
DECLARE @SourceCustomerID INT
DECLARE @TargetCustomerID INT
SELECT
@SourceCustomerID = 11000
, @TargetCustomerID = 99999
-- Obviously it is important to run the entire operation
-- in a transaction and implement TRY CATCH to rollback
-- the transaction when an error occurs. For instance
-- a hash collision could occur (but the chance that it
-- happens is astronomically small).
BEGIN TRY
BEGIN TRAN
--\
---) 2a. Copy [SalesOrderHeader]
--/
INSERT INTO [dbo].[SalesOrderHeader]
( [SalesOrderGUID]
, [RevisionNumber]
, [OrderDate]
, [DueDate]
, [ShipDate]
, [Status]
, [OnlineOrderFlag]
, [SalesOrderNumber]
, [PurchaseOrderNumber]
, [AccountNumber]
, [CustomerID]
, [SalesPersonID]
, [TerritoryID]
, [BillToAddressID]
, [ShipToAddressID]
, [ShipMethodID]
, [CreditCardID]
, [CreditCardApprovalCode]
, [CurrencyRateID]
, [SubTotal]
, [TaxAmt]
, [Freight]
, [TotalDue]
, [Comment]
, [rowguid]
, [ModifiedDate]
)
SELECT
CONVERT(UNIQUEIDENTIFIER, HASHBYTES('MD5', CONVERT(CHAR(36), src.[SalesOrderGUID]) + @NewGuidSalt)) AS [SalesOrderGUID]
, [RevisionNumber]
, [OrderDate]
, [DueDate]
, [ShipDate]
, [Status]
, [OnlineOrderFlag]
, [SalesOrderNumber]
, [PurchaseOrderNumber]
, [AccountNumber]
, @TargetCustomerID AS [CustomerID]
, [SalesPersonID]
, [TerritoryID]
, [BillToAddressID]
, [ShipToAddressID]
, [ShipMethodID]
, [CreditCardID]
, [CreditCardApprovalCode]
, [CurrencyRateID]
, [SubTotal]
, [TaxAmt]
, [Freight]
, [TotalDue]
, [Comment]
, NEWID() AS [rowguid]
, GETDATE() AS [ModifiedDate]
FROM
[dbo].[SalesOrderHeader] src
WHERE
src.CustomerID = @SourceCustomerID
--\
---) 2b. Copy [SalesOrderDetail] rows
--/
INSERT INTO [dbo].[SalesOrderDetail]
( [SalesOrderGUID]
, [SalesOrderDetailGUID]
, [CarrierTrackingNumber]
, [OrderQty]
, [ProductID]
, [SpecialOfferID]
, [UnitPrice]
, [UnitPriceDiscount]
, [LineTotal]
, [rowguid]
, [ModifiedDate]
)
SELECT
CONVERT(UNIQUEIDENTIFIER, HASHBYTES('MD5', CONVERT(CHAR(36), src.[SalesOrderGUID]) + @NewGuidSalt)) AS [SalesOrderGUID]
, NEWID() AS [SalesOrderDetailGUID]
-- For [SalesOrderDetailGUID] we simply use NEWID(), because it is not used somewhere else.
, src.[CarrierTrackingNumber]
, src.[OrderQty]
, src.[ProductID]
, src.[SpecialOfferID]
, src.[UnitPrice]
, src.[UnitPriceDiscount]
, src.[LineTotal]
, NEWID() AS [rowguid]
, GETDATE() AS [ModifiedDate]
FROM
[dbo].[SalesOrderDetail] src
JOIN
[dbo].[SalesOrderHeader] h
ON h.SalesOrderGUID = src.SalesOrderGUID
WHERE
h.CustomerID = @SourceCustomerID
COMMIT TRAN;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
RAISERROR(@ErrorMessage, 16, 1);
END CATCH
--\
---) 3. Now view the results
---) Note that the order detail rows are copied using the
---) same generated guid for [SalesOrderGUID]. Note that
---) if you would rerun the script starting with
---) “2. Demonstrate ..” you would get a second copy of the
---) same orders with orderdetails rows and with a different
---) generated guid for [SalesOrderGUID], due to the Salt
---) that was used.
--/
SELECT *
FROM
[dbo].[SalesOrderHeader] h
WHERE
h.CustomerID IN (@SourceCustomerID, @TargetCustomerID)
SELECT d.*
FROM
[dbo].[SalesOrderDetail] d
JOIN
[dbo].[SalesOrderHeader] h
ON h.SalesOrderGUID = d.SalesOrderGUID
WHERE
h.CustomerID IN (@SourceCustomerID, @TargetCustomerID)
GO
--\
---) 3. CLEANUP
--/
USE [tempdb]
GO
IF OBJECT_ID('[dbo].[SalesOrderHeader]', 'U') IS NOT NULL
DROP TABLE [dbo].[SalesOrderHeader];
IF OBJECT_ID('[dbo].[SalesOrderDetail]', 'U') IS NOT NULL
DROP TABLE [dbo].[SalesOrderDetail];
GO
Download the script here.
TSQL-E04-script
© 2018 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.
Simple setup for two lookup transformations using the Adventureworks database
A “join” of a nvarchar to a nchar column ..
.. will not work !
Example of a derived column that converts the value of a column of the 
Jobs created for the demo ..
Generating a CREATE script for the jobs ..
As you can see in the output window, multiple replacements will be applied one after the other ..
Replacement in the TSQL jobstep ..
Replacement in the PowerShell jobstep..
No replacements done on the default instance where the CREATE script was generated ..