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?
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With