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 *


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


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


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


–Repeat Step 2:

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


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


–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:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s