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)
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
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
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.
The simplest method which we use is something like below
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.
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:
The tool I wrote is a perfect fit.
sqlcmd to export and import data.
You just provide *.sql file with query and connect parameters to it.