Hey, wondering if any fellow old-schoolers ran into this issue, and if this is a SQL 2005 glitch or whether I did something wrong.
This is really a "post-mortem", as I seem to have fixed the issue.
So I am maintaining a legacy app for one of my clients - VB6 with SQL 2005 backend.
A requested application update called for adding 4 new fields to a particular table.
Did the usual ALTER TABLE ADD ... and as expected, expanding the table's columns shows the 4 new fields at the end.
The code uses in-line SQL, and btw it uses this alternative INSERT/SELECT syntax: "INSERT tablename SELECT col1 = value, col2 = value, etc."
I happened to place the code for new fields above the last one, not at the very end - i.e.:
The existing code had:
INSERT tablename SELECT col1 = value, col2 = value, ... coln = value
My modification initially was:
INSERT tablename SELECT col1 = value, col2 = value, ... newcol1 = value, newcol2 = value, newcol3 = value, newcol4 = value, coln = value
Shouldn't matter, right?
Well, there is an insert trigger on this table that will prevent the insert if "coln" has a value it doesn't like.
And when the insert is attempted, the trigger throws an exception because it thinks coln has an invalid value.
Yet a Debug.Print of the SQL INSERT statement shows that the columns have the right values.
Head-scratcher...
So, just for kicks, I changed my SQL to:
INSERT tablename SELECT col1 = value, col2 = value, ... coln = value, newcol1 = value, newcol2 = value, newcol3 = value, newcol4 = value
And what do you know? - the trigger was happy and did not throw an exception, and all appeared well.
Any thoughts on this? Was there a glitch in SQL 2005 triggers that was corrected later?
No glitch - if you don't specify a column list in the insert statement then SQL assumes the values being inserted are in the correct column order.
Because the query had the columns out of order it failed when a value in newcol1 did not meet the requirements to insert into coln.
For future reference, it is always a good idea to specify the column list in the insert statement:
INSERT INTO tablename (col1, col2, col3, ..., newcol1, newcol2, newcol3, coln)
SELECT col1, col2, col3, ..., newcol1, newcol2, newcol3, coln);
Wow - I just confirmed this - looks like that funky INSERT/SELECT syntax does not respect the column names and just inserts the values specified in that order, regardless of the "colname = " in front of the value. As I mentioned this is legacy code and this is the way some of the inserts were written (not how I would have done it) - very enlightening indeed!
To wrap this up with a neat little bow, this has been a good teachable moment for me.
The form of the INSERT statement that is being used in this legacy app is composed of two parts: the SELECT part, which treats the "colname = " part simply as a column alias.
In a SSMS query window, as you know you can run a SELECT query with no table, such as "SELECT 1, 2". You can give these values aliases, such as "SELECT x = 1, y = 2".
So this form of the INSERT statement says: "INSERT TableName (the SELECT statement that generates a list of values)"
Which I came to realize is the same as doing an INSERT statement without the column references, thereby defaulting to the ordinal position of the columns, i.e.: "INSERT TableName (list of values)".
Had I originally authored this, I naturally would have used the recommended explicit column list form: "INSERT (column list) VALUES (value list)".
Thank you for the feedback - and glad I could help.