From the Community: Using Column Names with INSERT

In "Using the INSERT Statement," InstantDoc ID 98366 (http://www.sqlmag.com/Articles/ArticleID/98366/98366.html), 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:

DECLARE @MyTable TABLE(     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())SELECT * FROM @MyTable

The results of the SELECT statement would be something like

ColumnIDColumnData ColumnDate--------------------------------------------------------------1           Row 1     2008-02-05 13:53:51.7602           Row 2     2008-02-05 13:53:51.7603           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