Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Copy table to a different database on a different SQL Server

Tags:

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.

like image 540
Keith Maurino Avatar asked Mar 02 '09 18:03

Keith Maurino


People also ask

How do I copy a table from one database to another in SQL server?

Connect the SQL Server instance, Open the Object Explorer and select the database. Right-click the database, select Tasks, and then click on Generate Scripts, Click on “Next”. On the Choose Object page, choose Script entire database and all database objects or Select specific database objects option and Click Next.

Can we copy data from one database to another?

Open SSMS. Right-click the Database you wish to copy data from. Select Generate Scripts >> Select Specific Database Objects >> Choose the tables/object you wish to transfer.


2 Answers

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] 
like image 66
James Avatar answered Oct 28 '22 14:10

James


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.

like image 29
Frank Baker Avatar answered Oct 28 '22 12:10

Frank Baker