CODEWITHSUNDEEP
sqlsql-server
devam demo
devam demo

Reputation: 63

Copy rows from 1 table and Insert it to another in different Server

I have a table with million records.

But, I just want to copy first 10000 rows and insert it to another table which is in different server in sql server management studio.

Server1
Db1
Table : table1

Server2
Db1
Table : table1

I want to copy 10000 rows from table1 in Db1 and insert it to table1 in Db2 from Server1 to Server2.

I know this query syntax, but i dont know how can i deal with different servers 

INSERT table1 (Col1, Col2, ..., ColN)
  SELECT Col1, Col2, ..., ColN
  FROM table2
  WHERE ...

Upvotes: 3

Views: 3835

Answers (3)

Ryan Intravia
Ryan Intravia

Reputation: 420

Updated the answer to include how you would do it across a linked server. The 'Insert Into' statement allows you to insert into a linked server just as you would into another table on the same server, a temp table, a table variable, etc. As long as the select contains the correct number of columns and corresponding data types of the second table the following pseudo code works: 

Insert Into LinkedServer.Db2.DbSchema.Table1
Select Top 10000 
   Column1,
   Column2,
   ...
From Db1.Table1
Where ...

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

In addition to the other answers given involving linked servers, you can right click on your database in SSMS and use the Import/Export Wizard to move rows from one server to another.

In the wizard you can specify a query that will return only the top 10k rows that you want to export.

Upvotes: 2

JamieA
JamieA

Reputation: 2013

on your first instance, you can add a linked server to your second instance, and then reference it with 4 part naming . You can add a linked server by expanding the server objects node, and then right click 'Linked Servers'

Then your query would look like

INSERT YourlinkedServer.YourDB.YourSchema.table1 (Col1, Col2, ..., ColN)
  SELECT TOP 1000 Col1, Col2, ..., ColN
  FROM table2
  WHERE ...

Your query could also be run by inserting the remote data into a local table

INSERT table1 (Col1, Col2, ..., ColN)
      SELECT TOP 1000 Col1, Col2, ..., ColN
      FROM YourlinkedServer.YourDB.YourSchema.table2
      WHERE ...

linked servers https://msdn.microsoft.com/en-GB/library/ms188279.aspx

Upvotes: 2

Related Questions