SQL Server bulk export large Table from one SQL Server to a different SQL Server with WHERE clause ?

Question

SQL Server bulk export large Table from one SQL Server to a different SQL Server with WHERE clause ?

Hello we have a large Table source in SQL Server VM_1 that needs to be copied to another different destination in SQL Server VM_2 with a WHERE clause.

What's the recommended way of doing this...SQL Server Management Studio -> Tasks -> Export Data... -> "Write a query to to specify the data to transfer" ...will this have an impact on Transaction Log and if yes on which VM_1 or VM_2 and is this method stable being it could run for hours or anything else that might cause it to fail ?

Or export to a flat file first or something different ?

Thanks in advance.

Answer 1

Generally, reading data does not write to the transaction log. But a long-running query could have secondary effects, particularly if you have some sort of snapshot isolation enabled.

If the this export/import is the only activity going on the server, you may try to do it directly. But if there are other users active, it may be better to export to file first and then import to the file.

Overall, the question is difficult to answer short and concisely, because there are so many "it depends" here.

Answer 2

Guoxiong 8,056

I would try to use bcp Utility to copy the rows to the bcp file and use a format file to bulk import to the detonation table.

Answer 3

Seeya Xi-MSFT 15,351 Microsoft Vendor

Hi @techresearch7777777 ,

Firstly, using the Export Data wizard can cause a significant increase in the transaction log size on both VM_1 and VM_2. This is because the wizard reads data from VM_1 and writes it to VM_2 using a single transaction, which can result in a large transaction log. You can mitigate this by using the "Fast Load" option in the wizard, which minimizes logging during the transfer.

Secondly, the Export Data wizard may not be the most efficient method for large data sets, especially if the transfer needs to run for hours. In this case, it may be better to use a bulk copy program, such as the bcp utility, to export the data to a flat file and then import it into the destination database. This method can be faster and less resource-intensive, but it does require more manual configuration.

Ultimately, the best method depends on your specific requirements and the size of the data set. If you have a small to medium-sized data set and want a simple, easy-to-use method, the Export Data wizard can be a good choice. If you have a large data set and need more control over the transfer process, bcp or another bulk copy program(SSIS) may be more suitable.

Best regards,

Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

techresearch7777777 1,406 Reputation points

2023-03-01T08:38:18.0966667+00:00

Thanks all for your replies trying to help, much appreciated.

Bulk export the all of the records of this large table-data seems more straight forward...but factoring the WHERE clause in filtering the data (still large amounts of records)...any further thoughts ?
Erland Sommarskog 72,971 Reputation points MVP

2023-03-01T21:21:19.3133333+00:00

You can use the queryout option with BCP:

BCP "SELECT ... FROM db.dbo.tbl WHERE .." queryout file.bcp -n -T -S Server

You could also set up a view and run bcp from the view, which makes the bcp command line sleaker.
Seeya Xi-MSFT 15,351 Reputation points Microsoft Vendor

2023-03-02T07:18:47.8666667+00:00
Hi @techresearch7777777 ,

If you still want to use bulk insert, here is a sample for your reference. You can change the options and parameters to suit your needs.

BULK INSERT TableName FROM 'FilePath' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2, MAXERRORS = 0, BATCHSIZE = 10000, TABLOCK, FIRE_TRIGGERS, KEEPIDENTITY, CHECK_CONSTRAINTS, ORDER (column_name [ASC|DESC], ...) ) WHERE column_name = value;

Best regards,

Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.
techresearch7777777 1,406 Reputation points

2023-03-02T20:25:29.46+00:00

Sounds like from your sample 1st step would be to bulk export ALL the Table's data and then use your BULK INSERT with WHERE clause to filter out only needed rows...

A two step process?

Thanks Seeya Xi-MSFT

Answer 4

I think Seeya was a little bit too imaginative there. The BULK INSERT command does not take any WHERE clause.

You can use SELECT * FROM OPENROWSET(BULK) and in this case you can apply a WHERE clause. But obviously, it it's better to apply the WHERE clause when you export the data.

If you are not acquainted with BCP and the other ways for bulk load and export, I have an article on my web site with all the details you need: https://www.sommarskog.se/bulkload.html.

SQL Server bulk export large Table from one SQL Server to a different SQL Server with WHERE clause ?

4 answers

Activity