Move Query Result from One Server to Another Table on Another Server in SQL Server

exportimportsql serversql-server-2008-r2

What are my options in moving result of a query in one server to another table on another server? Data contains ~400.000 rows so it won't be a quick one. (Both of them are SQL Server 2008 R2)

Thanks all.

Best Answer

One option is to use the SqlBulkCopy class via SQLCLR. You can either code this yourself or simply download the Free version of SQL# (which I am the author of) which contains a stored procedure named DB_BulkCopy which encapsulates this functionality. In either case the assembly will need to be given a PERMISSION_SET of EXTERNAL_ACCESS.

If using the SQL# stored procedure, you would do something similar to the following:

DECLARE @MyQuery NVARCHAR(MAX);

SET @MyQuery = N'select some stuffs; -OR- EXEC SomeProc;';

EXEC SQL#.DB_BulkCopy
    --@SourceType = N'MSSQL',
    --@SourceConnection = N'Context Connection = true;',
    @SourceQuery = @MyQuery,
    @DestinationConnection =
           N'Data Source=(local); Integrated Security=true; Initial Catalog=tempdb;', -- CHANGE!!
    @DestinationTableName = N'SchemaName.TableName',
    @BatchSize = 3000,
    @NotifyAfterRows = 1000,
    @TimeOut = 1000 -- seconds
    --@ColumnMappings = N'',
    --@BulkCopyOptionsList = N'TableLock|UseInternalTransaction' -- Optional; see descripton below
    --@RowsCopied = @Rows OUTPUT

@BulkCopyOptionsList values:

  • TableLock = Obtain a bulk update lock for the duration of the bulk copy operation. When not specified, row locks are used.
  • UseInternalTransaction = When specified, each batch of the bulk-copy operation will occur within a transaction.
  • additional options described in the SQL# manual.
Related Question