How to copy a table from database A on a linked server to database B?

Question

I am using SQL Server 2012 and I need to write a T-SQL query (that I will use as a SQL job) to copy a table (say, T1) located in a database (say, db1) on a linked Server to another database (say, db2).

Table T1 does not currently exist in database db2.

Assuming the table schema of T1 on the linked server is [xxx.xx.x.xx].db2.dbo.T1, how do I write this T-SQL query?

Please check [this](https://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server) — Fmanin, Jan 17 '18 at 07:39
Possible duplicate of [Copy tables from one database to another in SQL Server](https://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server) — Richard, Jan 17 '18 at 07:45

score 1 · Accepted Answer · answered Jan 17 '18 at 07:46

1

You may use OPENDATASOURCE. In Your destination Server database, Just Run

SELECT 
    *
    INTO dbo.DestinationTable
    FROM 
       OPENDATASOURCE
       (
          'SQLOLEDB', 
          'Data Source=SourceServer;User ID=MyUser;Password=MyPass'
       ).SourceDatabase.dbo.SourceTable;

answered Jan 17 '18 at 07:46

Jayasurya Satheesh

7,826
3
22
39

If I understand well, the Data Source=SourceServer will be Data Source=[xxx.xx.x.xx] based on my example. – user3115933 Jan 17 '18 at 08:33
Yes, Maybe without Square brackets – Jayasurya Satheesh Jan 17 '18 at 08:41
It is in the form of an IP Address...so I guess I will need those square brackets. – user3115933 Jan 17 '18 at 08:57
In that case Yes I guess. Try with both – Jayasurya Satheesh Jan 17 '18 at 08:57
It worked without the square brackets! I do have a small question: If I schedule this query to run as a daily job, will it still work the next day if, after the first run, the table has already been created in the target database? – user3115933 Jan 17 '18 at 09:34
1

It should work unless you made some changes in the Server side like User Credentials update – Jayasurya Satheesh Jan 17 '18 at 09:41

How to copy a table from database A on a linked server to database B?

1 Answers1