I have a 32 column table. Every column is NOT NULL, and all but the first have DEFAULT constraints. In particular, the 21st column has such a constraint. I also have a stored proc which truncates the table and then loads it with a SELECT from a view, like this
TRUNCATE TABLE NDRS_Call_Data_Table
INSERT INTO NDRS_Call_Data_Table(<column-list>)
SELECT <column-list> FROM NDRS_Call_Data_View
The view is a complex join of several other tables. When I run the proc, I get the error:
"Cannot insert the value NULL into column 'CC_Time', table 'Tomcat_prod.dbo.NDRS_Call_Data_Table'; column does not allow nulls. INSERT fails."
BUT, if I simply do this from Query Analyzer......
INSERT INTO NDRS_Calls_Data_Table(data_indicator)
it works. The default constraint on the CC_Time column works correctly and supplies the default value of '0 ' as it should. (the column is a CHAR(2) column, despite the name suggesting it is datetime)
Recompiling the view and the stored procedure does not help. Anybody else seen this? Is this a known bug?
You constraint works fine. I believe the null value comes from the view. As you said, the view was created from a complex join. Somewhere in the resultset it returns a null value in the view. I would run the query and check the resultset closely.
Got it. Misunderstanding on the operation of DEFAULT constraints. They don't override explicit nulls if you specify the column in the insert. They only provide values if you leave out the column in the column list. Thanks
Further research shows that this is ANSI standard behavior. A NOT NULL column with a DEFAULT will not use the default if the column is specified in the insert's column list and NULL is provided. Besides being counter-intuitive, frankly this strikes me as dumb. It really gets in the way of using the INSERT .... SELECT syntax
Undortunately, when using INSERT ... SELECT you can't say
SELECT ...., ISNULL(column, DEFAULT), ...
the way you can use the DEFAULT keyword like this....
You have to know what the default value is, and explicitly put it in as the 2nd parameter of the ISNULL function. So if you ever change the default constraint, you'll have to go back and change all the canned queries, too.
you can declare a variable for each column that you want default to be inserted in place of null, and then initialize them according to each variable data type:
declare @value int
select @value = cast(replace(replace(m.text, ')', ''), '(', '') as int)
from syscomments m,syscolumns c,sysobjects o