I have a requirement to insert the literal value 'NULL' into a nvarchar column as a default value. But when I insert records the default is going as db NULL instead of the literal 'NULL'. Can someone tell me where I am going wrong?
3 Answers
So you want the actual string of "NULL" to be inserted? Try something like this:
create table NullTable
(
nvarchar(100) not null default 'NULL',
.... -- your other fields
)
EDIT: Here is a full solution
create table InsertNull
(
Nullfield nvarchar(100) not null default 'NULL',
someint int not null
)
go
insert into insertnull(someint)
select 20
select *
from InsertNull
3 Comments
VKarthik
shark- I do have a table with the same setup. In the same example say our table contains two fields field1 with default of 'NULL', field2 INT. if I run the query say - insert into nulltable (field1) select 20 then field1 is still showing as NULL DB value
Nick O
@VKarthik are you looking to have the single quotes stored in the column too? Because what Shark has will have the default of a string NULL not DB
NULL.Note the quotes:
INSERT INTO yourtable (varcharfield) VALUES ('NULL'); // string null
INSERT INTO yourtable (varcharfield) VALUES (NULL); // actual null
If set the default in code instead of the designer is should work.
ALTER TABLE [myTable] ADD DEFAULT ('NULL') FOR [TextColumn]
'NULL'instead ofNULL? This sounds like a questionable decision to me.