Create batch INSERT statements instead of single INSERT for each row

csand

Hello

We use SQL Data Compare to generate Scripts for populating initial data our databases.
Some of the tables are having quite a lot of rows (~ 20000) and so the script gererated becomes quite big.

I was looking for an Option where I can tell SQL Data Compare to use Batch INSERT Statements instead of generating one INSERT Statement for each row to insert into a table.

Here is an example:
Instead of using:

INSERT INTO table1 (Column1, Column2) VALUES (100, 101);
INSERT INTO table1 (Column1, Column2) VALUES (200, 201);
INSERT INTO table1 (Column1, Column2) VALUES (300, 301);

it would be great to have an Option to get a script that looks like :

INSERT INTO table1 (Column1, Column2) 
VALUES 
(100, 101)
, (200, 201)
, (300, 301);

There a at least 2 advantages:
1) The generated script will be much smaller (especially if you have tables with a lot of columns)
2) Running the script on a database to fill the tables will be significantly faster

Until now I was unable to find such an Option.
Does anyone know if there is such an option in SQL Data Compare?

Tianjiao_Li

Hi @csand

Thanks for the suggestion! Unfortunately this is not possible to do in SQL Data Compare at the moment. Can you cast you vote on this post on our user voice forum? https://redgate.uservoice.com/forums/147879-sql-data-compare/suggestions/3449997-make-insert-statements-more-efficient-using-shorte

Thanks!