0

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

Daniel Bragg
  • 1,773
  • 1
  • 12
  • 25

1 Answers1

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.

Andrei Rantsevich
  • 2,879
  • 20
  • 25