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,….)
‘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.