Prev: FAQ & Solution: How to generate a number automatically for the Primary Key when inserting a record?
Next: Basic SQL Question
From: Guineapig1980 on 22 Apr 2008 09:11
Hi all

I am trying to copy data from one database server to another. I only
want to copy one table's data, not the entire database. The part that
I am having trouble with is connecting from one database server and
connect to another then telling it to insert into the second database
server.

Not sure if this is how it works.

any help is appreciated.
thanks
Harold
From: Plamen Ratchev on 22 Apr 2008 09:49
You can add linked server and use it to copy the table data across server.
Creating linked server is done using sp_addlinkedserver:

EXEC sp_addlinkedserver 'RemoteServer', N'SQL Server'

Based on security settings you may need to map remote server logins. This is
done using sp_addlinkedsrvlogin:

EXEC sp_addlinkedsrvlogin 'RemoteServer', 'false', 'LocalUser',
'RemoteUser', 'RemotePassword'

Then you just run a normal query referencing the linked server table with 4
part name:

INSERT INTO TargetTable
SELECT <columns>
FROM RemoteServer.RemoteDB.dbo.RemoteTable

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 | 
Pages: 1
Prev: FAQ & Solution: How to generate a number automatically for the Primary Key when inserting a record?
Next: Basic SQL Question