How to copy a table in SQL Server correctly ?

2009-04-21


There is chance that we might need to make a copy of table in SQL Server. Most of time, maybe we can use the following script:

Select * INTO newTable FROM oldDB.dbo.oldTable
It is really simple, right ? But, Did you concern the Constraint ? For example: There is a primary key in old table, you can not get the same primary key in the new table due to the above way.

Firstly you need create a new table by manual if you need to do add Constraints such as Primary Key, Foreign Key etc (I said manually create just because at least you need to give new constraints a new name which is different the old constraint name because the constraint name is Unique in the same SQL Server DB)

Normally I used Sql server management studio to get the old table CREATE TABLE script, it is easy, just right click the old table and choose "Script Table As"...

Then I need to change the table name to new table name, change all constraint names to new contstraint names. Then I excuse it and get new table which has the same structure as the old table.

Now the new table is empty. I need to add data into it.

You can use the following script to insert data:

INSERT INTO newTable SELECT * FROM oldDB.dbo.oldTable
However, there is still one more thing: If these 2 tables have a Identity field, you can not use the above script directly. I have a example as the following:
set IDENTITY_INSERT [dbo].[newTable] ON

INSERT INTO [dbo].[newTable] ([ID], [CID] ,[SID] ,[NAME]) SELECT [ID] ,[CID] ,[SID] ,[NAME] FROM .[dbo].[oldTable]

set IDENTITY_INSERT [dbo].[newTable] OFF