Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Insert Data From One Server To Another?

If I want to run this sort of query in SQL Server, how can I do the same query from one server I am connected to to another?

I tried adding "[ServerName1]." before "[DatabaseName1].[dbo]..." and "[ServerName2]." before "[DatabaseName2].[dbo]..." but that didn't seem to work.

INSERT INTO [DatabaseName1].[dbo].[TableName]
           ([FieldName])
     SELECT [FieldName] FROM [DatabaseName2].[dbo].[TableName]

Is this possible?

like image 739
sooprise Avatar asked Jul 19 '10 21:07

sooprise


2 Answers

Yes you would use the server-name before the whole rest of object-name like:

myserver.mydatabase.dbo.mytable

However you first have to set up linked servers. Look up linked servers in BOL.

like image 105
HLGEM Avatar answered Sep 22 '22 03:09

HLGEM


If you have adhoc distributed queries enabled you can use OPENDATASOURCE. Setting up a linked server is another option. Not sure of the pros and cons of each approach.

INSERT INTO [DatabaseName1].[dbo].[TableName]
SELECT FieldName
FROM OPENDATASOURCE('SQLNCLI',
    'Data Source=Server\InstanceName;Integrated Security=SSPI')
    .DatabaseName2.dbo.TableName
like image 22
Martin Smith Avatar answered Sep 23 '22 03:09

Martin Smith