I’m curious as to why the insert and update commands in SQL have different syntax. So far I have been unable to find any satisfactory explanation. I personally have a gripe with the insert syntax in that the column names are separated from the inserted value. In wide tables this makes it difficult to alter existing stored procedures since you have to manually count fields or come up with a commenting scheme. Even then, you aren’t guaranteed you’ll find a bug at runtime.
I propose that in addition to the normal syntax
INSERT INTO Foo (ColumnA, ColumnB, ColumnC) VALUES ('First', 'Second', 'Third')
SQL also support an alternate syntax such as:
INSERT INTO Foo SET ColumnA = 'First', ColumnB = 'Second', ColumnC = 'Third'
This syntax is easily readable and much more maintainable than the existing syntax.
MySQL has already adopted this syntax but unfortunately SQL Server has not. In the meantime I’ll continue commenting my large inserts like this:
INSERT INTO Foo (ColumnA, ColumnB, ColumnC) VALUES ( 'First', -- ColumnA 'Second', -- ColumnB 'Third' -- ColumnC )