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 

Answer 2

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.