Reputation: 730
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
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
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