From the Community: Using Column Names with INSERT

In "Using the INSERT Statement," InstantDoc ID 98366 (, you said that the columns in an INSERT statement's VALUES clause must be specified in the same order in which they appear in the table. That's true, but only if you don't specify column names. You should have recommended using column names, as the following sample code shows, because that approach lets you change the table structure without changing the SQL clauses and makes better use of column default values:

     ColumnID int,
     ColumnData varchar(10),
     ColumnDate datetime

INSERT INTO @MyTable (ColumnID, ColumnData, ColumnDate)
   VALUES(1,'Row 1',GETDATE())
INSERT INTO @MyTable (ColumnData, ColumnID, ColumnDate)
   VALUES('Row 2',2,GETDATE())
INSERT INTO @MyTable (ColumnID, ColumnData, ColumnDate)
   VALUES(3,'Row 3',GETDATE())

The results of the SELECT statement would be something like

ColumnID	ColumnData 	ColumnDate
-------------	----------------	---------------------------------
1           		Row 1     	2008-02-05 13:53:51.760
2           		Row 2     	2008-02-05 13:53:51.760
3           		Row 3     	2008-02-05 13:53:51.760

(3 row(s) affected) 
Timo Kukkonen, System Specialist, MCSE

[email protected]

You make a good point. It's a better practice to specify the column names in the INSERT statement.

-- Michael Otey

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.