Data insertion problem - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Data insertion problem

Author

Topic

jyothi_jayanth
Starting Member

7 Posts

Posted - 2001-11-27 : 02:27:27

I have two Sql servers connected by network..
Can i insert data from one table to another table in another server using insert into table1 select * from table2..if so please give me syntax ..thanks in advance

Jayanthu Babu gadde

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-11-27 : 02:37:56

Jayanthu,

Have you got "Books on Line" (BOL)??

Anyway...


Insert server.database.owner.table (Vol1, Col2)
Select Col1, Col2 from server.database.owner.table

HTH

DavidM
It gets windy at a thousand feet...."Tutorial D"

smgirard
Starting Member

2 Posts

Posted - 2001-11-27 : 13:41:04

Sorry David about not agreeing with you, but your query won't work if you server is not added to sysservers. See the message below:

Server: Msg 7202, Level 11, State 2, Line 1
Could not find server 'ServerName' in sysservers. Execute
sp_addlinkedserver to add the server to sysservers.

To have an Ad Hoc query (select or insert) you can use 'OPENROWSET' like the following:

INSERT INTO TableName (DbName)
SELECT db.DbName
FROM OPENROWSET('SQLOLEDB','ServerName';'UserName';'Password',
' SELECT name as [DbName]
FROM master..sysdatabases') AS db

This request will insert in TableName all the database names of a specific server. It will work if the servers are connected by network (which is your case) and if you have the necessary permissions to do it ('sa' will be fine, of course).

You can replace 'ServerName' with the IP address.

Sylvain-Marc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources