Copy table to a different database on a different SQL Server.

Question

I would like to copy a table from one database to another. I know you can easily do the following if the databases are on the same SQL Server.

SELECT * INTO NewTable FROM existingdb.dbo.existingtable;

Is there any easy way to do this if the databases are on two different SQL Server's, without having to loop through every record in the original table and inserting it into the new table?

Also, this needs to be done in code, outside of SQL Server Management Studio.

Answer 1

Microsoft SQL Server Database Publishing Wizard will generate all the necessary insert statements, and optionally schema information as well if you need that:

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A

Answer 2

Yes. add a linked server entry, and use select into using the four part db object naming convention.

Example:

select * into targetTable from [sourceserver].[sourcedatabase].[dbo].[sourceTable]

Answer 3

Generate the scripts?

Generate a script to create the table then generate a script to insert the data.

check-out SP_ Genereate_Inserts for generating the data insert script.