Copy data from one SQL Server database table to the other
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 D开发者_高级运维B2..TBL1 (copy only data)
Now my question is copy the data from SERVER1 --> DB1--> TBL1 to SERVER2 --> DB2 -->TBL2
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
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
You can set up linked servers in SSMS and execute cross server queries.
精彩评论