Copy table to a different database on a different SQL Server

Solution 1:

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]

Solution 2:

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.

Solution 3:

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.