Insert Data From One Server To Another?

sql sql-server linked-server
sooprise picture sooprise · Jul 19, 2010 · Viewed 43.9k times · Source

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?

Answer

HLGEM picture HLGEM · Jul 19, 2010

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.

Related questions

How to select data of a table from another database in SQL Server?

Suppose that I have a database which name is testdb in test server. I also have a database named proddb in prod server. Now I want to select data of a table of testdb database from proddb database. How can …

Read More
Could not find server 'server name' in sys.servers. SQL Server 2014

I recently upgraded our SQL Server from 2005 to 2014 (linked server) and I am noticing that one of the stored procedures which calls the exec command to execute a stored procedure on the upgraded linked server is failing with the error …

Read More
How to execute store procedure for another DB?

I have a stored procedure that should be able to be executed on any table of any database on my MS Sql Server. Most of the combination of EXEC and USE statements didn't result in anything. Here is the stored …

Read More