sql server - 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.

Answer 1

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