Best way to copy records with Identity columns

Question

Post reply

Best way to copy records with Identity columns

mike 11318

Newbie

Points: 7
More actions
March 31, 2010 at 1:13 pm

#143725

I need to copy (or duplicate) a set of records for CompanyA and create a set of records for CompanyB. In my application, CompanyA is a real company who is willing to let their data be duplicated (and then desensitized) for use as a test user, CompanyB.
The Company table is the parent table in the set of records. I am able to create the first-level child records with 'INSERT INTO xx...SELECT'
ex. INSERT INTO Vendors (vendorName, companyAutoId) SELECT VendorName, 4 AS CompanyAutoId FROM Vendors WHERE CompanyAutoId=1 [1 is the key value for CompanyA; I inserted a Company record for CompanyB and its key is 4]
My challenge is the secondary child tables, the Transaction tables. For example, the Vendor table and the Products table have Identity fields for their key value (VendorMast, and ProductMast). The ProductTransaction table contains the VendorMast and ProductMast identity keys so I can Join the transaction to the Vendor and Product tables. Since I am copying the records from CompanyA to CompanyB, the Identity keys will get new values which will lose the relationship between the records for CompanyB.
After I create the first-level child table records (Vendor, Product, etc), then CompanyB is partialled setup. Next, I will construct a WHILE loop on the ProductTransaction table for CompanyA. For each record, I will need to write code to obtain the new VendorMast key, the new ProductMast key, etc; then finally INSERT the new ProductTransaction record.
Is a WHILE loop the best approach for this? Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

Use

SET IDENTITY_INSERT ON

and then copy the records along with identity column data.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

First of all, without posting other information like table definition/query, you made your post a bit complicated

so better to put this info to get result/response faster (Refer the link from my signature)

mike 11318 (3/31/2010)
The ProductTransaction table contains the VendorMast and ProductMast identity keys so I can Join the transaction to the Vendor and Product tables.

This join will happen between companyA and companyB tables

OR between comapnyB tables only ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150467 More actions · Answer 3

mike 11318 (3/31/2010)
I need to copy (or duplicate) a set of records for CompanyA and create a set of records for CompanyB

One simple approach is to script out all the table definitions, without any foreign key constraints, and use that to create empty tables in the new database. You should probably include (only) the clustered index definitions too, to make things more efficient in the next step.

Next, use a tool like the Import/Export Data Wizard to copy the data (only) from all the source tables to the new empty tables. Be sure to tick the 'enable identity insert' option. Also, ensure that the target database's recovery model is BULK_LOGGED or SIMPLE, so you can take advantage of minimally-logged inserts.

Finally, script out all the non-clustered indexes and foreign-key constraints (plus anything else you might need to transfer) on the source tables, and apply that script to the new tables.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Paul White NZ (4/2/2010)
mike 11318 (3/31/2010)
I need to copy (or duplicate) a set of records for CompanyA and create a set of records for CompanyB
One simple approach is to script out all the table definitions, without any foreign key constraints, and use that to create empty tables in the new database. You should probably include (only) the clustered index definitions too, to make things more efficient in the next step.
Next, use a tool like the Import/Export Data Wizard to copy the data (only) from all the source tables to the new empty tables. Be sure to tick the 'enable identity insert' option. Also, ensure that the target database's recovery model is BULK_LOGGED or SIMPLE, so you can take advantage of minimally-logged inserts.
Finally, script out all the non-clustered indexes and foreign-key constraints (plus anything else you might need to transfer) on the source tables, and apply that script to the new tables.
Paul

thats why i never miss your any post

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150467 More actions · Answer 5

Bhuvnesh (4/2/2010)
That's why i never miss your any post

Thank you, Bhuvnesh 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi