CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Saleem
Saleem

Reputation: 730

Query to copy rows from sql server to another sql server

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.

Upvotes: 0

Views: 210

Answers (2)

Singaravelan
Singaravelan

Reputation: 849

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 1

Related Questions