1

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.

1

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.
2

The simplest method which we use is something like below

  1. Create a table on destination server using the scripts generated from the table on the source server by right click and going through option of Tasks/Generate scripts.

  2. Then run the below T-SQL to upload the data from source table to destination.

    INSERT INTO dbo.YourTableNameHere SELECT * FROM [SourceServer].[SourceDatabase].dbo.YourTableNameHere

For large data movement, refer to below discussion and answer for same:

Copy a large data from sql query result

  • Unfortunately, I don't have an option to use Excel as there are more rows to move than can be handled (64k) – Stackoverflowuser Mar 5 '15 at 10:00
1

I restored the database's backup on the target server and issued an insert into select.

0

The tool I wrote is a perfect fit. I uses sqlcmd to export and import data. You just provide *.sql file with query and connect parameters to it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.