CODEWITHSUNDEEP
sqlsql-server-2008
Jeff
Jeff

Reputation: 882

Inserting the output of a query into a table created on a different server SQL Server 2008

I was wondering if someone can guide me as to how I can insert the output of a query into a table that I created on a different server from where I am running my query.

For example:

table is located on server1 called tbl1 in a database called database1.

the query that i am running is querying data located on server2.

for the insert command, would this work: server1.database1.tbl1

If you need more information please let me know.

Upvotes: 0

Views: 120

Answers (4)

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

Assuming you've created a linked server for server1, with appropriate permissions, you should be able to:

insert into server1.database1.dbo.tbl1
    select ...

Upvotes: 0

marc_s
marc_s

Reputation: 755043

As long as you can reach both servers, it should be easy enough:

 INSERT INTO server1.database1.dbo.tbl1(list of columns)
   SELECT
      (list of columns)
   FROM
      server2.database2.dbo.tbl2
   WHERE
      (some condition here)

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294407

Fully qualified remote names have 4 parts: servername.databasename.schemaname.tablename. You can do any operation with them, including INSERT-SELECT, as long as the linked server is properly configured for updates and the MSDTC is properly configured for the two servers to engage in a distributed transaction.

Upvotes: 1

Sem Vanmeenen
Sem Vanmeenen

Reputation: 2151

Your insert command on server2 would work but only if server1 is registered as linked server in server2.

Upvotes: 0

Related Questions