erikd (11/4/2013)
http://www.red-gate.com/products/sql-development/sql-prompt/entrypage/sql-performance-tips-ebookI was reading through, and got sort of confused about the 23rd point:
If you need to insert many rows at once into a table,
use, where possible, the multi-row VALUES clause in
INSERT statements.
Which I understand as just being
INSERT INTO TABLE (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
('value-2a', ['value-2b', ...]),
...
What situations does this apply to? Day to day, if I'm inserting any amount of data, it's either from table to table, or from a file. When would you have a need to write something like this out for a large amount of data?
Thanks
If you generate a datascript using the native tools in SQL Server, it'll generate an individual INSERT/VALUES statement for each row of data being scripted. I believe the example is just showing how bad things like that can be.
Of course, if it's a distribution script and the INSERT will create more than 10 or 12 rows, I tend to use a file and BULK INSERT. Some companies don't like you to put files on their boxes, though so I have also been known to generate code similar to the example given.
--Jeff Moden
Change is inevitable... Change for the better is not.