Copy Data from One SQL Instance to Another

February 3rd, 2009 by Sameer | Filed under Uncategorized.

When using SQL Server Management Studio you might want to copy data from one DB to another (using just a SELECT and INSERT statement)

It’s so easy you won’t believe it

sp_addlinkedserver @server='192.168.123.456', @provider='SQLNCLI',    @srvproduct='',@provstr='User Id=sa; Password=are-you-crazy-to-use-sa'

insert into [192.168.123.456].MYDB.dbo.tblRecords

select * from MYDB.dbo.tblRecords

and when you are done

sp_dropserver  [192.168.123.456]

References:
sp_addlinkedserver on MSDN

sp_dropserver on MSDN

Related Reading:

Head First Java, 2nd EditionHead First Java, 2nd Edition
Starting Out with Java: From Control Structures through Objects (4th Edition)Starting Out with Java: From Control Structures through Objects (4th Edition)
Effective Java (2nd Edition)Effective Java (2nd Edition)

Other Interesting Posts

«
»

2 Responses to “Copy Data from One SQL Instance to Another”

  1. abhijit says:
    June 1, 2010 at 12:04 pm

    error :(

    Msg 208, Level 16, State 1, Line 4
    Invalid object name ‘BuiltUP.dbo.tblRecords’.

    Reply
  2. abhijit says:
    June 1, 2010 at 12:05 pm

    sp_addlinkedserver @server=’192.168.2.134′, @provider=’SQLNCLI’,
    @srvproduct=”,@provstr=’User Id=sa; Password=saptech@123′

    insert into [192.168.2.134].BuiltUP.dbo.tblRecords

    select * from BuiltUP.dbo.tblRecords

    Reply

Leave a Reply