Copy table from one database to another

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

General SQL Server Forums

New to SQL Server Programming

Copy table from one database to another

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-02-29 : 08:04:16

I want to make an exact copy of a production database table in our development database.
1. Script the production table using Script Table as Create
2. Drop the existing development table
3. Use the Script (see 1. above) to recreate the development table (substituting the proper database name)
4. Insert all records from the production table into the development table (insert into table development_table select * from production_table)

Does this create an exact duplicate including the proper sequencing of identity fields? Meaning if there are deleted records in the production database table, their identity fields would be numbered something like 1, 2, 3, 5, 6, 9, 11, etc. I want to make sure that the identity fields are numbered exactly the same in the development table.

If not, how do I do that?
thanks in advance!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 08:10:05

If you want to keep identity values same as your prod data, you need to use SET IDENTITY_INSERT ON option before inserting data and make sure to turn it off after you done with inserting prod data.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-29 : 08:39:13

If you have access to .net programming tools, you put together a simple application to copy the data using SQLBulkCopy:

http://weblogs.sqlteam.com/jeffs/archive/2008/02/27/60539.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-02-29 : 10:25:31

Thank that's helpful.

The following statement doesn't work with set identity_on.
insert into table development_table select * from production_table

Error: An explicit value for the identity column in table 'photoeyedev.dbo.VS2_Templates' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Do I need to explicitly state the columns in the insert or since it's all columns and they are exactly duplicated, would the insert statement look like?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-02-29 : 10:52:50

The error message answers your question pretty well, doesn't it? yes, you need to explicitly state the columns.

quote:

Error: An explicit value for the identity column in table 'photoeyedev.dbo.VS2_Templates' can only be specified when a column list is used and IDENTITY_INSERT is ON.

- Jeff
http://weblogs.sqlteam.com/JeffS

RichardSteele
Posting Yak Master

160 Posts

Posted - 2008-02-29 : 11:10:11

This doesn't work. Same message

SET IDENTITY_INSERT [tabledev].[dbo].[VS2_Templates] ON
GO

INSERT [tabledev].[dbo].[VS2_Templates]
(Custnumber
,BgColor)
select custnumber, bgcolor from [tableproduction].[dbo].[VS2_Templates]

GO
SET IDENTITY_INSERT [tabledev].[dbo].[VS2_Templates] OFF
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-29 : 11:26:47

Works fine for me. Not sure why you are facing problem.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-01 : 21:10:38

You have to list all columns in the table, including identity column.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources