Auto generated INSERT statement is buggy with commas at start
The auto-generated INSERT statement BADLY MISALIGNS data when "Place commas at start" is enabled. I've listed all the settings to reproduce the issue at the end of this post.
The comma and associated info are on a separate line (almost useless - Worse when I need to delete some of the columns). See this example (generated by SQL Prompt Pro 5.1 - hyphens represent tabs due to HTML limitations):
INSERT INTO dbo.Test
---- (FirstName
---- , LastName
---- , LocationAddress
---- , LocationCity
---- , LocationState
---- , LocationZip)
VALUES (''
---- , -- FirstName - varchar(100)
---- ''
---- , -- LastName - varchar(100)
---- ''
---- , -- LocationAddress - varchar(100)
---- ''
---- , -- LocationCity - varchar(100)
---- ''
---- , -- LocationState - varchar(50)
---- '' -- LocationZip - varchar(10)
---- )
When "Place commas at end" is enabled it works correctly. Please fix the "place commas at start" option to work like this, but with preceeding commas of course:
INSERT INTO dbo.Test
---- (FirstName,
---- LastName,
---- LocationAddress,
---- LocationCity,
---- LocationState,
---- LocationZip)
VALUES ('', -- FirstName - varchar(100)
---- '', -- LastName - varchar(100)
---- '', -- LocationAddress - varchar(100)
---- '', -- LocationCity - varchar(100)
---- '', -- LocationState - varchar(50)
---- '' -- LocationZip - varchar(10)
---- )
You can reproduce this issue with the following settings from the SQL Prompt menus:
Format > Commas and Parentheses:
----"Place commas at start"
Inserted Code > Objects & Statements:
----"Insert full INSERT statement"
--------"Show column names" and/or "Show column data types"
--------"Insert default value for each column"
This is included in SQL Prompt 6.4, which you can download from http://www.red-gate.com/products/sql-development/sql-prompt/
-
Anonymous commented
Is this ever going to get fixed?
-
Stephen commented
Red Gate: IS ANYONE READING THESE COMMENTS AND SUGGESTIONS THAT ARE GETTING OLD?
IF YOU DO, PLEASE COMBINE THIS AND http://redgate.uservoice.com/forums/94413-sql-prompt-feature-suggestions/suggestions/1872709-moving-commas-from-end-to-start-is-always-bugg SO PERHAPS THE COMBINED 32 VOTES CAN HAVE MORE OF AN EFFECT!
SORRY TO SHOUT, BUT SOME FEEDBACK TO YOUR USERS' VOICES SEEMS APPROPRIATE...
-
Morgan Smith commented
one year later and STILL not fixed
-
Josh commented
Please also see this similar issue (vote for both if you agree):
---- Moving commas from "end" to "start" is always buggy when comments exist
-
Stephen commented
Agreed - absolute pain! I always use commas-before and have endured this since I can't remember.
PLEASE FIX :-)