CODEWITHSUNDEEP
ssmssql-insertssms-2014generate-scripts
Daniel Bragg
Daniel Bragg

Reputation: 1953

Using SSMS 2014, how to generate efficient insert scripts?

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?

generating INSERT scripts for tables in SSMS 2014Select tableenter image description here

Upvotes: 0

Views: 66

Answers (1)

Andrei Rantsevich
Andrei Rantsevich

Reputation: 2945

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.

Upvotes: 1

Related Questions