37

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.

AHiggins
  • 7,029
  • 6
  • 36
  • 54
Keith Maurino
  • 3,374
  • 10
  • 40
  • 48

6 Answers6

62

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]
James
  • 12,636
  • 12
  • 67
  • 104
4

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. enter image description here
  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. enter image description here
  3. In the Set Scripting Options window, specify the path where you will save the generated script file, and click Advanced. enter image description here
  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. enter image description here Getting back to the Advanced Scripting Options window, click Next.
  5. Review the Summary window and click Next. enter image description here
  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. enter image description here

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.

Karthikeyan P
  • 1,216
  • 1
  • 20
  • 23
3

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.

Community
  • 1
  • 1
Frank Baker
  • 146
  • 1
  • 4
2

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

ine
  • 14,014
  • 8
  • 55
  • 80
2

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.

Eppz
  • 3,178
  • 2
  • 19
  • 26
  • If you script large amounts of data, the SSMS won't be able to open the SQL. – jumxozizi Feb 05 '16 at 16:51
  • But you can still use `sqlcmd` from a command prompt to execute the script. – H. de Jonge Sep 14 '17 at 12:56
  • @Rubiksmomo - True, but also note that even if SSMS can open a large amount of data that the insert may still fail ('query completes with errors') when scripting even a _modestly large_ amount of data. This may happen even with a small number of columns (of narrow width, at that). – OcelotXL Sep 14 '17 at 14:43
1

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
James Jenkins
  • 1,954
  • 1
  • 24
  • 43