[sql-server] Copy table to a different database on a different SQL Server

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 Servers, without having to loop through every record in the original table and insert it into the new table?

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

If it’s only copying tables then linked servers will work fine or creating scripts but if secondary table already contains some data then I’d suggest using some third party comparison tool.

I’m using Apex Diff but there are also a lot of other tools out there such as those from Red Gate or Dev Art...

Third party tools are not necessary of course and you can do everything natively it’s just more convenient. Even if you’re on a tight budget you can use these in trial mode to get things done….

Here is a good thread on similar topic with a lot more examples on how to do this in pure sql.

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

Create the database, with Script Database as... CREATE To

Within SSMS on the source server, use the export wizard with the destination server database as the destination.

• Source instance > YourDatabase > Tasks > Export data
• Data Soure = SQL Server Native Client
  • Validate/enter Server & Database
• Destination = SQL Server Native Client
  • Validate/enter Server & Database
• Follow through wizard

SQL Server(2012) provides another way to generate script for the SQL Server databases with its objects and data. This script can be used to copy the tables’ schema and data from the source database to the destination one in our case.

1. Using the SQL Server Management Studio, right-click on the source database from the object explorer, then from Tasks choose Generate Scripts.
2. In the Choose objects window, choose Select Specific Database Objects to specify the tables that you will generate script for, then choose the tables by ticking beside each one of it. Click Next.
3. In the Set Scripting Options window, specify the path where you will save the generated script file, and click Advanced.
4. From the appeared Advanced Scripting Options window, specify Schema and Data as Types of Data to Script. You can decide from here if you want to script the indexes and keys in your tables. Click OK. Getting back to the Advanced Scripting Options window, click Next.
5. Review the Summary window and click Next.
6. You can monitor the progress from the Save or Publish Scripts window. If there is no error click Finish and you will find the script file in the specified path.

SQL Scripting method is useful to generate one single script for the tables’ schema and data, including the indexes and keys. But again this method doesn’t generate the tables’ creation script in the correct order if there are relations between the tables.

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.