In recent years I've developed a habit of formatting SQL SELECT queries like so:

    JOIN tableSource ON col1 = col2
    JOIN (
     ) AS subQuery ON subQuery.foo = col2
    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.

       (  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
    col1 = 'col1',
    col2 = 'col2',
    col3 = 'col3',
    // etc
    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?

  • This won't solve your particular problem, but may be useful to others - Notepad++ has a "Poor Man's T-sql Formatter" plugin available. It's not fancy, but it produces consistent output while wasting very few brain cells. – Dan Pichelman Jun 16 '13 at 14:09
  • When writing SQL I try to start each (major) clause on a new line. When I have to break a clause into multiple lines I always end a line on something incomplete so that it will be clear to the reader that they should continue reading. For example: select FieldNames / from Sources as S inner join / TableSource as TS on TS.Col1 = S.Col2 inner join / ( select FieldNames from OtherSources ) as Subquery on Subquery.Foo = S.Col2 / where Subquery.SomeField = SomeExpression. If I have to split a list of fields or values I'll end a line on a trailing comma. And always use aliases in JOINs. – HABO Jun 16 '13 at 16:48

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.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.