How can I copy data records between two instances of an SQLServer database

Question

I need to copy some records from our SQLServer 2005 test server to our live server. It's a flat lookup table, so no foreign keys or other referential integrity to worry about.

I could key-in the records again on the live server, but this is tiresome. I could export the test server records and table data in its entirety into an SQL script and run that, but I don't want to overwrite the records present on the live system, only add to them.

How can I select just the records I want and get them transferred or otherwise into the live server? We don't have Sharepoint, which I understand would allow me to copy them directly between the two instances.

score 50 · Accepted Answer · edited Feb 21 '13 at 07:07

50

If your production SQL server and test SQL server can talk, you could just do in with a SQL insert statement.

first run the following on your test server:

Execute sp_addlinkedserver PRODUCTION_SERVER_NAME

Then just create the insert statement:

INSERT INTO [PRODUCTION_SERVER_NAME].DATABASE_NAME.dbo.TABLE_NAME   (Names_of_Columns_to_be_inserted)
SELECT Names_of_Columns_to_be_inserted
FROM TABLE_NAME

edited Feb 21 '13 at 07:07

Sagar Upadhyay

829
2
11
31

answered Sep 30 '08 at 17:20

Kwirk

964
14
25

This is a great solution. Sometimes, I find SQL Server Import/Export wizard to be a pain. This is a really easy process. – JeremyCanfield Nov 10 '16 at 00:38

score 11 · Answer 2 · answered Sep 30 '08 at 17:06

11

I use SQL Server Management Studio and do an Export Task by right-clicking the database and going to Task>Export. I think it works across servers as well as databases but I'm not sure.

answered Sep 30 '08 at 17:06

Joe Phillips

44,686
25
93
148

1

Sadly that feature isn't available to me in SQLServer 2005. – Jaymie Thomas Sep 30 '08 at 17:17
1

+1, extremely fast and simple, it creates and runs an SSIS package that does the job. – Aki May 22 '14 at 12:43

score 3 · Answer 3 · answered Sep 30 '08 at 17:03

3

An SSIS package would be best suited to do the transfer, it would take literally seconds to setup!

answered Sep 30 '08 at 17:03

Mitchel Sellers

58,921
13
103
170

What would you recommend in case I have a hundred tables I need to copy? Backup/restore is bad option: 3 days for support to action, will need half a day to clean up from other data. Thanks. – Stoleg Jul 08 '15 at 17:31
You can setup multiple tables in a copy. SSIS packages are repeatable, which is good. – Mitchel Sellers Jul 08 '15 at 22:49

score 1 · Answer 4 · answered Sep 30 '08 at 17:05

I would just script to sql and run on the other server for quick and dirty transferring. If this is something that you will be doing often and you need to set up a mechanism, SQL Server Integration Services (SSIS) which is similar to the older Data Transformation Services (DTS) are designed for this sort of thing. You develop the solution in a mini-Visual Studio environment and can build very complex solutions for moving and transforming data.

How can I copy data records between two instances of an SQLServer database

4 Answers4

Linked