Copy data from one table and save it into another table in different database on different SQL Server

Question

I have two different databases in two different SQL Servers. The databases are identical in schema but contain different data in one of the tables.

I want to copy all the data from one table in one database to the same table in the other database so that I can get rid of the database from which I am copying the data.

The data is too large so I cannot create data scripts and run it onto other database.

How can I achieve this?

Write a simple program to do this. Using program to select and update the records is easy. — Kason, Mar 02 '16 at 09:15
"The data is too large so I cannot create data scripts" - how large? — Mitch Wheat, Mar 02 '16 at 09:19
Two options - Linked server. SSIS. By the way - who upvoted? and why? I don't see any research effort in the question — Raj, Mar 02 '16 at 09:30

score 2 · Answer 1 · edited May 23 '17 at 11:50

2

There are many ways like ssis transfer,select * into ,but i prefer below way if you are just transferring data

create a linked server on source server for destination server,then you could refer destination server with four part name

Assuming linked server of source is A and destination server is B,data moving is as simple as

insert into B.databasename.Schema.Table
select * from table---this is in source server and db

if data is huge and you may worry about time outs,you can write a simple script which can do in batches like

 While (1=1)
    begin
    insert into B.databasename.Schema.Table
    select  top 10000* from table---this is in source server and db
    if (@@rowcount=0)
    break
    end

Creating linked server ,you can follow this

edited May 23 '17 at 11:50

Community

1
1

answered Mar 02 '16 at 09:31

TheGameiswar

27,855
8
56
94

Thanks for this. The table has xml column therefore distributed query is not working – Fahad Abid Janjua Mar 02 '16 at 11:43
you can try export import wizard,i am not sure if it works with xml columns,you can do the same by right clicking database ->tasks->export data – TheGameiswar Mar 02 '16 at 11:50

score 0 · Answer 2 · answered Apr 13 '16 at 13:02

You have the following options available to you. Not all of these will work, depending on your exact requirements and the networking arrangements between the servers.

SQL Server Management Studio - Import & Export Wizard: this is accessed from the right-click menu for a database > Tasks > Import Data (or Export Data).
SQL query using a Linked Server: a Linked Server configured between the two servers allows you to reference databases on one from the other, in much the same way as if they were on the same server. Any valid SQL query approach for transferring data between two tables within one database will then work, provided you fully-qualify the table names as Server.Database.Schema.Table.
SSIS: create an SSIS package with both servers as connections, and a simple workflow to move the data from one to the other. There is plenty of information available online on how to use SSIS.
Export to flat-file format then import: this could be done using the Import/Export Wizard above or SSIS, but instead of piping the data directly between the two servers, you would output the data from the source table into a suitable flat-file format on the filesystem. CSV is the most commonly used format for this. This file can then be moved to the destination server using any file transfer approach (compressed e.g. to a Zip file if desired), and imported into the destination table.
Database backup and restore: Similar to (4), but instead of using a flat file, you could create a backup of the source database via Tasks > Back Up... You then move that backup as a file (just like the CSV approach), and restore it onto the destination server. Now you have two databases on the destination server, and can move data from one to the other locally.

score 0 · Answer 3 · answered Oct 12 '19 at 07:12

0

I hope, this query helps you!!!

INSERT INTO [dbo].[tablename] (Column1, Column2,Column3)
(select Column1, Column2,Column3, from [Database1].[dbo].[tablename]

Thanks!!!

answered Oct 12 '19 at 07:12

Nitika Chopra

1,281
17
22

Copy data from one table and save it into another table in different database on different SQL Server

3 Answers3