Sql-server – Copying distinct rows from one table to another

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.

You could use SQL Server Management Studio's "Export Data" task. Right click on the database in SSMS, select Tasks and then "Export Data". It will launch an interactive wizard that will allow to copy tables from one server to another, although you will have to recreate the indices yourself. The wizard creates a temporary SSIS package (although you can also opt to save it) and is relatively fast.