Using SSMS 2014, how to generate efficient insert scripts?

Question

When generating INSERT scripts for tables in SSMS 2014, the output of the generated script is in the format of:

INSERT [schema].[table] (<column_list) VALUES (column_values)
GO
INSERT [schema].[table] (<column_list) VALUES (column_values)
GO

While this gets the job done, it is horribly slow. We can manually re-tool the script to be in the format of:

INSERT [schema].[table] (<column_list>)
VALUES (column_values)
,(column_values) -- up to 1000 rows
GO
INSERT [schema].[table] (<column_list>)
VALUES (column_values)
,(column_values) -- up to 1000 rows
GO

We've noted an increase in speed of more than 10x by changing the script in this manner, which is very beneficial if it is a script that needs to be re-run occasionally (not just a one-time insert.)

The question is, is there a way to do this from within the SSMS script generation, or alternately, is there a process that can convert the script that is in the first format into a script in the second format?

Andrei Rantsevich · Accepted Answer · 2019-11-21 04:06:51Z

1

I develop SSMSBoost add-in. We have feature named Results Grid Scripter, that can produce virtually any script that you want based on the data from Results Grid:

https://www.ssmsboost.com/Features/ssms-add-in-results-grid-script-results

There are several pre-defined templates and you can change them to get exactly that you need.

answered Nov 21, 2019 at 4:06

Andrei Rantsevich

2,97523 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using SSMS 2014, how to generate efficient insert scripts?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related