Multiple format options for insert statements
Currently SQL Prompt only has one option for formatting insert statements which poses a problem for us with multi value set inserts.
Ideally we'd be able to select a formatting for a single value set insert and another format for a multi value set insert.
This would allow us to do something like this:
-- Single Value Set Insert
INSERT INTO TestTable (TestName,
~~~~TestValue,
~~~~TestDate)
VALUES (N'Something',
~~~~140,
~~~~'1/1/2019')
-- Multi Value Set Insert
INSERT INTO TestTable (TestName, TestValue, TestDate)
VALUES (N'Something', 140, '1/1/1900'),
~~~~(N'SomethingElse', 150, '1/2/1900'),
~~~~(N'SomethingElse2', 160, '1/2/2000')
The issue for us is that we have need of the single value set insert format in our stored procedures and most of our normal code as it allows for only one direction of scrolling (up/down instead of up/down and side to side).
But in some population and testing scripts we really need the multi value set insert format (or something close to it) to prevent the scripts from being ridiculously long and for ease of identifying where one value set ends and another starts.
It would be great if SQL Prompt gave us support for this.
Colby Severson
shared this idea
-
Jeff Humphreys
commented
Here's a wacky extension of this, and it's primarily for debugging and finding the error, or enhancement and adding new rows to the VALUES.
What if the column names and the individual values aligned? Very out there, I know.
But for cases where values were transposed, it would help identify where that was happening. Or a misspell on a value, or the wrong value in a long list of value sets. The one wrong one, the wrong decimal place, the reversed mm/dd instead of dd/mm.