Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

This question already has an answer here:

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?

share|improve this question
    
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
add comment

marked as duplicate by Justin Cave, gnat, MichaelT, Dynamic, GlenH7 Jun 16 '13 at 14:39

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

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.

share|improve this answer
add comment

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