Love Script as Insert, but just found odd behavior

GarryB

I was just doing some work and using the handy feature script as insert.
So I ran a select top 1000 query and get the following scripted insert output:


I then realized the table had more than a 1000 records and I increased my TOP to 10,000.
Then I get this output for the script as insert:


So for everything over 1000 that format of the insert would make it hard to strip out the value for each row which is what I was trying to do.

Is this by design?

Michael Clark

Hi @GarryB,

Thanks for getting in touch. Yes you are 100% correct, the maximum number of rows you can insert in one statement is 1000 when using INSERT INTO ... VALUES ... . When there are more than 1000 rows we use the full INSERT syntax.

I've got a couple of quick questions if that's okay:

Would you prefer the full INSERT statement to be on the same line? Are you able to achieve that with the formatting settings? What's the procedure to "strip out the value for each row"?

Best,
Michael

GarryB

I guess I could find and replace to remove the INSERT INTO and VALUES to nothing.

I actually just need the actual data in the () for what I am trying to accomplish.

So I ended up breaking it up. Do 1000, then sort desc and grab the extra 346 values.

mikecare

Would love if number of records over 1000 to group the inserts into batches of 1000;  Love this feature, at least now I know why the different formats.