COPYING ROWS FROM ONE TABLE IN A SERVER TO ANOTHER TABLE IN A SERVER

There can be situations where we need to copy a few rows from

 Server S -> Database S -> Table S       (to)           Server D->Database D-> Table D

This can happen when we have updated table with current values in our dev server that we need to move over to production.  The steps below describe the T-SQL method of performing the move.

–Step 1:  set identity_insert DestinationTable on

–Step 2: insert into [DestinationDatabase].dbo.[DestinationTable](Col1, Col2, Col3,….)

select *

from OPENDATASOURCE(‘SQLNCLI’,

‘Data Source=SourceServer\SourceInstance;Integrated Security=SSPI’)

.[SourceDatabase].dbo.[SourceTable]

where col1=’Filter to avoid duplicate inserts into the destination table’

If you get the  error: Msg 15281, Level 16, State 1, Line 2

SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

Follow the below steps to fix the issue.

–Step 3: sp_configure

If you cant see the ‘Ad Hoc Distributed Queries’ listed in the result set then

–Step 4: sp_configure ‘show advanced options’, 1

reconfigure

–Step 5: sp_configure ‘Ad Hoc Distributed Queries’, 1

reconfigure

–Repeat Step 2:

–Step 6: sp_configure ‘Ad  Hoc Distributed Queries’, 0

reconfigure

–Step 7: sp_configure ‘show advanced options’, 0

reconfigure

–Step 8:set identity_insert DestiationTable off

Execute a select statement for the table and VOILA!

You have successfully appended the destination table with new records copied from source table.

Reference Resources:

http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

Share this post: on Twitter on LinkedIn