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
