How to transfer one table from server 1 to the table in server 2?

Question

Suppose I have two database on two separate servers:

server1:
ip 127.0.0.1
user1, passwd1
db1.tbl1

server2:
ip 127.0.0.2
user2, passwd2
db2.tbl2

Suppose tb1 and tb2 has the same table structure. I've search the web, people suggest to set up linked-servers, but I don't want to set up.

Since I know the password and ip address, things should go very easy like this:

insert into db2.tbl2 
    select *  
    from db1.tbl1

However I don't know where to put the ip address and username and password.

Why not just extract the data from table 1 to csv, then use insert into table 2? Linked servers can be very slow.. — Josh Adams, Feb 12 '18 at 12:58
@JoshAdams I just want to know how to do it, where to put the password and username. I know how to do it offline. — an offer can't refuse, Feb 12 '18 at 13:00
@buzhidao if you don't want to set up linked server, have u tried `look up` transformation in SSIS and `merge` tables — Ven, Feb 12 '18 at 13:44

score 2 · Accepted Answer · answered Feb 12 '18 at 13:26

2

Try this (run it on server1):

INSERT INTO [db1].[tbl1]
SELECT * FROM OPENDATASOURCE(
    'SQLOLEDB',
    'Data Source=127.0.0.2;Initial Catalog=db2;Persist Security Info=True;User ID=user2;Password=passwd2'
).db2.tbl2

You can refer to this article.

answered Feb 12 '18 at 13:26

Michał Turczyn

32,028
14
47
69

sepupic · Answer 2 · 2018-02-12T14:50:46.763

0

You tagged your question with many RDBMS.

My answer is about SQL Server.

Since I know the password and ip address, things should go very easy like this: insert into db2.tbl2 select * from db1.tbl1

And this is exactly the syntax you can use in case of linked servers. You incapsulate connection string in the definition of linked server, then the only thing that changes is linked server name in your code:

insert into db2.tbl2 select * from server1.db1.tbl1

example of linked server setup:

EXEC master.dbo.sp_addlinkedserver @server = N's_2005', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'127.0.0.1,8851'
go
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N's_2005',@useself=N'False',@locallogin=NULL,@rmtuser=N'login1',@rmtpassword='*****'
go

select top 5 * from [s_2005].[Northwind].[dbo].[Employees]

If you want other solutions, you can use OPENROWSET (Transact-SQL)

providing the whole connection string in your INSERT every time you want to access remote server:

select a.* from openrowset('SQLNCLI', 'Server=127.0.0.1,8851;Trusted_Connection=no;uid=login1;pwd=*****;', 'select top 5 * from [Northwind].[dbo].[Employees]') as a

The third option is SSIS where you incapsulate connection strings into SourceConnection and DestinationConnection

Here is a picture of the same result using linked server and openrowset:

edited Feb 12 '18 at 14:50

answered Feb 12 '18 at 13:09

sepupic

8,409
1
9
20

SO where should I put the ip address, user name and passwd exactly? – an offer can't refuse Feb 12 '18 at 13:11
I gave you 3 choices, what choice you are talking about? – sepupic Feb 12 '18 at 13:15
I think the first is the easiest one, otherwise I would like to download the data and then upload to the destination table. – an offer can't refuse Feb 12 '18 at 13:18
Sorry? All 3 require you to take data from 1 server and load to another, there are no miracles or "metadata switch only". Do you ask me how to set up linked server? What is your future linked server, SQL Server? – sepupic Feb 12 '18 at 13:21
I am asking if there is a single line of code such as `insert into db2.tbl2 select * from server1.db1.tbl1`, no need to set up, using other software, etc... – an offer can't refuse Feb 12 '18 at 13:23
I already answered you, or you set up linked server and then use it, or you use OPENROWSET and even there "there is only 1 line", but this line will include your ip and credentials, I provided the link – sepupic Feb 12 '18 at 13:25

score 0 · Answer 3 · answered Jan 30 '19 at 14:22

If you are using SQL Server Management Studio then there is a wizard to import/export data between servers and databases:

https://learn.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-import-and-export-wizard?view=sql-server-2017

How to transfer one table from server 1 to the table in server 2?

3 Answers3