Import data from another server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Import data from another server

Author

Topic

jabedin
Starting Member

28 Posts

Posted - 2004-02-24 : 19:56:18

Dear All,

I need to import data from a SQL server Database(server-1, with read access) to another SQL Server(Server-2). I am using Insert Into statement as I will eventually import data as a daily basis with selected fields and date range. I need the qualifer syntext where I am putting my FROM Clause

INSERT INTO ..........
....

Select ........

from "???????????"
Any help regarding this will be highly appreciated.

.....

. Any help regarding this will be highly appreciated.

I am using insert into

Jay
Bella Vista
NSW-2153

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 20:02:18

First you need to have a linked server setup. Then reference the linked server in the INSERT INTO SELECT FROM query.

INSERT INTO DB1.dbo.Table1 (Column1, Column2)
SELECT Column1, Column2
FROM LinkedServerName.DB1.dbo.Table1

So in my example, the linked server would exist on the server where the data is being copied to. The linked server would reference the server where the data is being copied from. It can go either way though:

INSERT INTO LinkedServerName.DB1.dbo.Table1 (Column1, Column2)
SELECT Column1, Column2
FROM DB1.dbo.Table1

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 20:08:55

Just realized that your question is in the Access forum. Is this an Access question or SQL Server question?

Tara

jabedin
Starting Member

28 Posts

Posted - 2004-02-24 : 21:04:39

Thank you very much for your quick response. The SQL Server from where the data will be extracted from has the Number adderss(like 10.197.12.111 where I have a read access). I tried something in the FROM clause like

FROM 10.132.12.111.DB2.dbo.table1.

It generates the error Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '10.197'.

Do I need a name than a number for a server address.

Regards
Jay

quote:
Originally posted by tduggan

First you need to have a linked server setup. Then reference the linked server in the INSERT INTO SELECT FROM query.

INSERT INTO DB1.dbo.Table1 (Column1, Column2)
SELECT Column1, Column2
FROM LinkedServerName.DB1.dbo.Table1

So in my example, the linked server would exist on the server where the data is being copied to. The linked server would reference the server where the data is being copied from. It can go either way though:

INSERT INTO LinkedServerName.DB1.dbo.Table1 (Column1, Column2)
SELECT Column1, Column2
FROM DB1.dbo.Table1

Tara

Jay
Bella Vista
NSW-2153

jabedin
Starting Member

28 Posts

Posted - 2004-02-24 : 21:05:54

I am sorry, It sould is a SQL Server question?

quote:
Originally posted by tduggan

Just realized that your question is in the Access forum. Is this an Access question or SQL Server question?

Tara

Jay
Bella Vista
NSW-2153

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-25 : 12:11:11

You need to create a linked server in order to be able to run a query like this. Look up linked servers in SQL Server Books Online for information about how to create one.

Tara

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-26 : 11:44:08

1st...sp_addlinkedserver @server=[10.132.12.111]

2nd...INSERT INTO x (Column1, Column2)
select (Column1, Column2) FROM [10.132.12.111].DB1.dbo.Table1
should work.

jabedin
Starting Member

28 Posts

Posted - 2004-02-26 : 16:56:48

Thanks Andrew. Looks like I am very close now. I am getting the following error message.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Jay
Bella Vista
NSW-2153

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources