July 23, 2009 at 7:21 am
Well, probably many of you won't find it strange, but I was surprised of the behaviour of INSERT without insert list when computed columns are involved:
CREATE TABLE #test (
column1 int,
column2 int,
column3 int
)
INSERT INTO #test VALUES (1,2,3)
INSERT INTO #test VALUES (3,4,5)
INSERT INTO #test VALUES (6,7,8)
INSERT INTO #test VALUES (9,10,11)
SELECT *
INTO #test_copy
FROM #test
ALTER TABLE #test ADD compColumn AS 0
INSERT INTO #test
SELECT * FROM #test_copy
INSERT INTO #test VALUES (12,13,14)
ALTER TABLE #test ADD column4 int
INSERT INTO #test VALUES (15,16,17,18)
I would have expected all the insert statements to fail, but they're not.
I've been working with SQLServer since SQL 7 days, but I noticed it today...
Does it look strange to you too or it's only me?
(Edit: fixed garbled code)
-- Gianluca Sartori
July 23, 2009 at 8:10 am
from BOL (about Computed Columns):
A computed column cannot be the target of an INSERT or UPDATE statement.
[font="Arial Narrow"]bc[/font]
July 23, 2009 at 8:29 am
Yes, this is obvious.
What's less obvious is why no error is raised when insert list contains more items than the select list.
I would have expected any insert to fail without explicit insert list.
-- Gianluca Sartori
July 23, 2009 at 8:33 am
also from BOL (re: Inserts):
If a column is not in column_list, the SQL Server 2005 Database Engine must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. The Database Engine automatically provides a value for the column if the column:
Is a computed column. The calculated value is used.
[font="Arial Narrow"]bc[/font]
July 23, 2009 at 8:45 am
Once again this is obvious...
I'm sorry, maybe I'm not able to pose my question clearly enough:
1) Why don't insert statements fail even if I try to insert 3 values in a table that has 4 columns without specifying the insert list?
CREATE TABLE #test (
column1 int,
column2 int,
column3 int,
compColumn AS 0
)
INSERT INTO #test VALUES (12,13,14)
2) Why don't insert statements fail even if:
a) I try to insert 4 values in a table that has 5 columns without specifying the insert list
b) I provide a select list with columns ordered differently from the target table's columns ?
CREATE TABLE #test (
column1 int,
column2 int,
column3 int,
compColumn AS 0,
column4 int
)
INSERT INTO #test VALUES (12,13,14,15)
-- Gianluca Sartori
July 23, 2009 at 8:58 am
there is no requirement that the insert statement have an explicit column list, although its obviously best practice. as long as the datatypes are compatible and there is not more or less "insertable" columns than the target, it assumes the order of the columns in the table. the engine is generating the computed column itself, as it should.
[font="Arial Narrow"]bc[/font]
July 23, 2009 at 9:18 am
OK, so I suppose this doesn't look strange to anyone but me.
Thanks for your answers, bc
-- Gianluca Sartori
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply