Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Peter Smith
Starting Member
8 Posts |
 Posted - 2009-12-17 : 17:24:19
|
| the companytype column in table prospects is of type integer with a default value of 0Im trying to insert records from table testprospectimport to table prospects.Now if column companytype in testprospectimport is null I get this error:Cannot insert the value NULL into column 'companytype', table 'prospects'; column does not allow nulls. INSERT fails. Warning: Null value is eliminated by an aggregate or other SET operation. The statement has been terminated.insert into prospects(companytype)select targetTbl.companytypefrom testprospectimport srcTbl left join prospects targetTbl on srcTbl.companyname=targetTbl.companynamewhere targetTbl.companyname Is nullIm trying that IF the column in the source table testprospectimport is null the default value of the targetcolumn in prospects should be used (in this case 0), but how?insert into prospects(companytype)select isnull(targetTbl.companytype,<USE TARGET COLUMN DEFAULT))from testprospectimport srcTbl left join prospects targetTbl on srcTbl.companyname=targetTbl.companynamewhere targetTbl.companyname Is nullPS. I wanted to insert table create statement, but that's simply too large... |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-12-17 : 18:22:37
|
| Just put the default value in the isnull statement.select isnull(targetTbl.companytype,'0')An infinite universe is the ultimate cartesian product. |
 |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
|
|
|
|
|
if trying to insert null value use column default