Write INSERT statements with values next to column names?

Question

When writing an INSERT statement with a lot of columns, it would be nice to have the value next to the column name like in an UPDATE statement. Something like:

insert into myTable
set
  [col1] = 'xxx',
  [col2] = 'yyy',
  [col3] = 42
  etc...

Are there any tricks to mimic this?

I thought I was onto something with this:

insert into myTable
select
  [col1] = 'xxx',
  [col2] = 'yyy',
  [col3] = 42
  etc...

But the aliases aren't actually being associated with the insert table's columns and if someone added a new column to the table it could really screw things up. Anyone have any other ideas of how one could do this?

Answer 1

The closest you'll get would be to specify the columns on the insert (this will protect you from your concern about a new column being added) and alias the values on the select (which gives you some degree of self-documenting code).

insert into myTable
    ([col1], [col2], [col3])
    select 'xxx' as [col1], 'yyy' as [col2], 42 as [col3]

Answer 2

For big inserts, I've done fancy (and perhaps overfly fussy) layouts. Some examples:

INSERT MyTable ( MyTableId,        Name,             Description,      SomeStringData1
                ,SomeStringData2,  SomeStringData3,  SomeStringData4,  MoreStringData1
                ,MoreStringData2,  MoreStringData3,  MoreStringData4,  SomeNumericData1
                ,SomeNumericData2, SomeNumericData3, SomeNumericData4, MoreNumericData1
                ,MoreNumericData2, MoreNumericData3, MoreNumericData4, BigBlobAA
                ,BigBlobBB,        EnteredAtDate,    UpdatedAtDate,    RevisedAtDate
                ,NeedAnotherDate )
 values
               ( @MyTableId,        @Name,             @Description,      @SomeStringData1
                ,@SomeStringData2,  @SomeStringData3,  @SomeStringData4,  @MoreStringData1
                ,@MoreStringData2,  @MoreStringData3,  @MoreStringData4,  @SomeNumericData1
                ,@SomeNumericData2, @SomeNumericData3, @SomeNumericData4, @MoreNumericData1
                ,@MoreNumericData2, @MoreNumericData3, @MoreNumericData4, @BigBlobAA
                ,@BigBlobBB,        @EnteredAtDate,    @UpdatedAtDate,    @RevisedAtDate
                ,@NeedAnotherDate )

This works if you're pretty darn certain that you wont ever be inserting columns or otherwise modifying what is being inserted. It gets gets everything on one screen, and makes it fairly simple to pick out what value goes into which column.

If inserted values are likely to change or are complex (such as case statements), I do the following (outdent all but every fifth item):

INSERT MyTable
  (
     MyTableId
    ,Name
    ,Description
    ,SomeStringData1
   ,SomeStringData2
    ,SomeStringData3
    ,SomeStringData4
    ,MoreStringData1
    ,MoreStringData2
   ,MoreStringData3
    ,MoreStringData4
    ,SomeNumericData1
    ,SomeNumericData2
    ,SomeNumericData3
   ,SomeNumericData4
    ,MoreNumericData1
    ,MoreNumericData2
    ,MoreNumericData3
    ,MoreNumericData4
   ,BigBlobAA
    ,BigBlobBB
    ,EnteredAtDate
    ,UpdatedAtDate
    ,RevisedAtDate
   ,NeedAnotherDate
  )
 values
  (
     MyTableId
    ,Name
    ,Description
    ,SomeStringData1
   ,SomeStringData2
    ,SomeStringData3
    ,SomeStringData4
    ,MoreStringData1
    ,MoreStringData2
   ,MoreStringData3
    ,MoreStringData4
    ,case
       when something then 'A'
       when orOther   then 'B'
       else 'Z'
     end
    ,SomeNumericData2
   ,SomeNumericData3
    ,SomeNumericData4
    ,MoreNumericData1
    ,MoreNumericData2
    ,MoreNumericData3
   ,MoreNumericData4
    ,BigBlobAA
    ,BigBlobBB
    ,EnteredAtDate
    ,UpdatedAtDate
   ,RevisedAtDate
    ,NeedAnotherDate
  )

(After adding that CASE statement, I "counted indents" to make sure I had everything lined up properly.)

It takes a bit of effort to get things layed out properly, but it can make maintenance, support, and subsequent modification simpler.