Copy Parent-Child records with identities

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Copy Parent-Child records with identities

Author

Topic

ocajuno
Starting Member

5 Posts

Posted - 2008-08-12 : 10:16:34

I have a couple related tables with identities for keys. I'd like to copy the parent record and the related child records.

Table1 contains Table1ID (identity) and Table2Id (FK to Table2) columns.
Table2 contains Table2ID (identity).

I'd like to make a copy of the Table1 and Table2 records without using a cursor. Is this possible? How?

Also, can a temp table be avoided?

Thanks, Randy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 13:22:33

1.script out both the tables and apply them with new names (Table1New,Tble2New say)
2.SET IDENTITY_INSERT ON for Table2New and populate it with data from Table2

INSERT INTO Table2New
SELECT * FROM Table2
3.SET IDENTITY_INSERT Table2New OFF

4..SET IDENTITY_INSERT ON for Table1New and populate it with data from Table1

INSERT INTO Table1New
SELECT * FROM Table1
5.SET IDENTITY_INSERT Table1New OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources