copying data from one server to another

copying data from one server to another

am 22.04.2008 15:11:12 von Guineapig1980

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

Re: copying data from one server to another

am 22.04.2008 15:49:52 von Plamen Ratchev

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
FROM RemoteServer.RemoteDB.dbo.RemoteTable

HTH,

Plamen Ratchev
http://www.SQLStudio.com