Migrating old data into new while keeping track of foreign keys

Question

Post reply

Migrating old data into new while keeping track of foreign keys

tarr94

SSCommitted

Points: 1684

March 4, 2020 at 8:18 pm

#3731047

Hello,

I'm working on migrating some data from an old system into a newer one. Their table definitions are the same, but I'm not sure of the best way to bring the data over while allowing them to retain their foreign key relationships.

In this simplified example below, I have a table for sales orders, and a table for sales order notes. I want to add the sales orders and sales order notes from the old system into the new system. In doing so, I need to make sure that the notes from the old system continue to be matched up with the correct sales order. Since the incoming sales order notes will not have the same PK (NoteID) value as before (due to an auto-incrementing PK), I'll have to update the incoming sales orders to account for these new values.

I suspect the answer might involve a cursor that loops through the order notes, capturing their old and new PK values, then updating the order table, but I'm not entirely sure what this would look like, or if this is in fact the most ideal approach. I greatly appreciate any suggestions on this!

/*
Old system - Order and Order notes
*/
CREATE TABLE #Order_OldSystem
(
OrderID INT,
OrderNum VARCHAR(10),
NoteID INT
)

INSERT INTO #Order_OldSystem VALUES (1,'ABC',NULL)
INSERT INTO #Order_OldSystem VALUES (2,'DEF',1)
INSERT INTO #Order_OldSystem VALUES (3,'GHI',2)
INSERT INTO #Order_OldSystem VALUES (4,'JKL',3)

CREATE TABLE #OrderNote_OldSystem
(
NoteID INT,
Note VARCHAR(50)
)

INSERT INTO #OrderNote_OldSystem VALUES (1, 'This order is invalid.')
INSERT INTO #OrderNote_OldSystem VALUES (2, 'Buyer wishes to cancel this order.')
INSERT INTO #OrderNote_OldSystem VALUES (3, 'Adding a test note.')

/*
New system - Order and Order notes
*/
CREATE TABLE #Order_NewSystem
(
OrderID INT,
OrderNum VARCHAR(10),
NoteID INT
)

INSERT INTO #Order_NewSystem VALUES (1,'321',NULL)
INSERT INTO #Order_NewSystem VALUES (2,'654',NULL)
INSERT INTO #Order_NewSystem VALUES (3,'987',1)

CREATE TABLE #OrderNote_NewSystem
(
NoteID INT,
Note VARCHAR(50)
)

INSERT INTO #OrderNote_NewSystem VALUES (1, 'Follow up with customer in two weeks.')

/*
Add the old system's orders and order notes into the new system
*/

--Current information
SELECT * FROM #Order_OldSystem
SELECT * FROM #OrderNote_OldSystem
SELECT * FROM #Order_NewSystem
SELECT * FROM #OrderNote_NewSystem

--Expected list of sales orders
SELECT OrderID = 1, OrderNum = '321', NoteID = NULL
UNION
SELECT OrderID = 2, OrderNum = '654', NoteID = NULL
UNION
SELECT OrderID = 3, OrderNum = '987', NoteID = 1
UNION
SELECT OrderID = 4, OrderNum = 'ABC', NoteID = NULL
UNION
SELECT OrderID = 5, OrderNum = 'DEF', NoteID = 2
UNION
SELECT OrderID = 6, OrderNum = 'GHI', NoteID = 3
UNION
SELECT OrderID = 7, OrderNum = 'JKL', NoteID = 4

--Expected list of sales order notes
SELECT NoteID = 1, Note = 'Follow up with customer in two weeks.'
UNION
SELECT NoteID = 2, Note = 'This order is invalid.'
UNION
SELECT NoteID = 3, Note = 'Buyer wishes to cancel this order.'
UNION
SELECT NoteID = 4, Note = 'Adding a test note.'

DROP TABLE #Order_OldSystem
DROP TABLE #OrderNote_OldSystem
DROP TABLE #Order_NewSystem
DROP TABLE #OrderNote_NewSystem

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101099 More actions · Answer 1

>> Since the incoming sales order notes will not have the same PK (NoteID) value as before (due to an auto-incrementing PK) <<

Turn off the auto-incrementing for the rows you're bringing over from the old table. If the new tables are empty, you can just do a simple:

SET INSERT_IDENTITY dbo.Order_NewSystem ON

INSERT INTO dbo.Order_NewSystem ( ...col_list... )

SELECT ...col_list...

FROM dbo.Order_OldSystem

SET INSERT_IDENTITY dbo.Order_NewSystem OFF

And ditto for the Notes table.

If the new table has some existing rows in it, you'll have to futz with ids, by adding say MAX(new_table_id) + 1000000 to the old ids. When the old rows have been added, re-sync the identity columns to the new max values.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

Looks like a duplicate post, Tarr94, take a look at what i posted on your origonal post https://www.sqlservercentral.com/forums/topic/sql-server-data-migration#post-3729874

***The first step is always the hardest *******

tarr94 SSCommitted Points: 1684 More actions · Answer 3

Thanks for the responses, I'll take a look at both of these. Sorry SGT, I never caught your last post on that thread, probably due to the gap in time, but it does appear quite helpful.