Query to copy rows from sql server to another sql server

Question

I have two sql servers, SE1 and SE2, they are basically having the same schema, but each one has its own usability. I need to find a query to copy some rows from SE1.Table1 Where Destination Like '%HR%' to SE2.Table2. Is there such query ?? Thx in advance.

Check http://ask.sqlservercentral.com/questions/28851/how-i-can-copy-records-from-one-server-to-other-se.html — MusicLovingIndianGirl, Feb 24 '14 at 12:16
http://stackoverflow.com/questions/603502/copy-table-to-a-different-database-on-a-different-sql-server — Hamidreza, Feb 24 '14 at 12:21

score 1 · Accepted Answer · edited Feb 24 '14 at 13:34

1

I find the easiest way is to add a remote connection from one to the other. So, go to the second server and do:

sp_addlinkedserver SE1

Then you can go to the database you want to use and do something like:

insert into database.dbo.Table1(<column list>)
    select <column list>
    from SE1.database.dbo.Table1
    where col like '%HR%';

This uses the four-part naming convention to access the remove table.

edited Feb 24 '14 at 13:34

Saleem

709
2
13
34

answered Feb 24 '14 at 12:25

Gordon Linoff

1,242,037
58
646
786

I tried this method, but it seems that there's something wrong. when I execute SELECT * FROM [SE1].[DB1].[dbo].[Table1] WHERE col Like '%HR%' I get the following error: Could not find server 'SE1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Although I used sp_helpserver and I found the name listed! – Saleem Feb 24 '14 at 13:35
You should post this question on dba.stackexchange.com, with more details about your configurations and the information in your comment. I would vote to migrate the question, but it lacks a lot of information so it won't be well received. – Gordon Linoff Feb 24 '14 at 13:46

score 1 · Answer 2 · answered Feb 26 '14 at 05:01

SELECT * into tr FROM    OPENROWSET( 'SQLOLEDB',
                'Server=10.10.1.89\SQLEXPRESSR2;UID=sa;PWD=password',                  
                'SET FMTONLY OFF;SET NOCOUNT ON; exec DBASE89.dbo.Getdata @UID=''21'''
              )

select * from tr

consider 10.10.1.89\SQLEXPRESSR2 as remote server, we need to create stored procedure with select command for required data. Here tr is Temp table, when execute above query the result will stored in tr table. From this tr table we can copy to required table in local server.

Note: in 10.10.1.89\SQLEXPRESSR2 server, need to enable the OleAutomationEnabled set as True in SurfaceAreaConfiguration.

Query to copy rows from sql server to another sql server

2 Answers2

Linked