Copy data from one SQL Server database table to the other

Question

I would like to copy the data from one table to another between different servers.

If it is with in the same server and different databases, i have used the following

SELECT * INTO DB1..TBL1 FROM DB2..TBL1 (to copy with table structure and data)

INSERT INTO DB1..TBL1(F1, F2) SELECT F1, F2 FROM DB2..TBL1 (copy only data)

Now my question is copy the data from SERVER1 --> DB1--> TBL1 to SERVER2 --> DB2 -->TBL2

Answer 1

If the two servers are set up as Linked Servers in SQL Server then you can use the fully qualified name.

Insert Into Server1.Database1.dbo.Table1 (Col1, Col2)
Select Col1, Col2 From Server2.Database2.dbo.Table2

You can also right click a database and go to Tasks -> Import Data or Export Data (In SQL 2000 the menu option is called All Tasks)

This will launch wizard and perform the import/export for you.

EDIT:

Here is a link for creating linked servers - http://msdn.microsoft.com/en-us/library/ms190479.aspx

You can see a list of servers by executing

select * from sys.servers

Or via the Server Objects > Linked Servers folders

HTH

Answer 2

I guess . I am a little too late on this question :-), but you can try using SSIS in case the two servers are not set up as Linked Servers