Scenario:  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 a Development/ Test 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