In recent years I’ve developed a habit of formatting SQL SELECT queries like so:
SELECT
fieldNames
FROM
sources
JOIN tableSource ON col1 = col2
JOIN (
SELECT
fieldNames
FROM
otherSources
) AS subQuery ON subQuery.foo = col2
WHERE
someField = somePredicate
So you see my pattern: each keyword is on its own line and that keyword’s fields are indented by 1 tab-stop and the pattern is used recursively for sub-queries.
This works well for all of my SELECT queries, as it maximizes readability though at the cost of vertical space; but it doesn’t work for things like INSERT and UPDATE which have radically different syntax.
INSERT INTO tableName
( col1, col2, col3, col4, col5, col6, col7, col8 )
VALUES ( 'col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8' ),
VALUES ( 'col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8' )
UPDATE tableName
SET
col1 = 'col1',
col2 = 'col2',
col3 = 'col3',
// etc
WHERE
someField = somePredicate
As you can see, they aren’t as pretty, and when you’re dealing with tables with a lot of columns they quickly become unweildly.
Is there a better way to format INSERT and UPDATE? And what about CREATE statements and other operations?
2
I don’t think the Update example you gave is really any different than the Select statement. Keeping one field per line works regardless of the number.
The insert statement is another matter. The example you gave isn’t something that goes into production for most programs. This could be a way to hardcode values into some reference table. Usually it’s some script for data management. For an application, the values are usually set to a parameter.
Insert into MyTable(Field1) Values(@Field1Value);
Like most things, this is easy to manage when you have a few fields, but when you have a lot, it becomes a problem. I think the key to any solution is a way to manage the one-to-one relationship with the field list and the values. Keeping a reasonable number on each line (3-8 depending on the length of the names) as long as you are consistent. Another advantage of parameters, is their names can have a lot more meaning if they are close to the field name (I think my example achieves that.).
If the list of inserts gets too long, you would be better off pulling those values from an external file (text, spreadsheet) that has some sort of field naming structure:
Insert into MyTable (Field1)
select OtherField1 from ExternalSourceFile.
I realize the question wants to manage this in an actual code statement, but typing the data into something like a spreadsheet is easier to view and manage and the code is much easier to read. Also, you can alter the data without having to change the sql statement.