How to set default value while insert null value into not null column SQL Server?
Solution 1
First Solution,
insert into t1
select id,isnull(name,'Peter') from t2
Second solution
ALTER TABLE T1 ALTER COLUMN name varchar(255) NULL
insert into t1
select id,name from t2
ALTER TABLE T1 ALTER COLUMN name varchar(255) NOT NULL
Solution 2
So instead of
Insert into t1 select * from t2
you can rewrite your query as
Insert into t1
select col1,col2, ISNULL(name, 'Peter'), othercolumns from t2
Solution 3
Use COALESCE
Query
INSERT INTO t1(Id, Name)
SELECT Id, COALESCE(Name, 'Peter') FROM t2;
Or you can use a CASE
expression.
Query
INSERT INTO t1(Id, Name)
SELECT Id, CASE WHEN Name IS NULL THEN 'Peter' ELSE Name END
FROM t2;
DineshDB
Working as a Senior Software Engineer at Hexaware Technologies, Chennai with proficient knowledge in SQL Server,T-SQL. Having much interest in Logical code writing. SQL is one of the programming language,where one spends more timethinking than typing. -Philip Greenspun
Updated on June 04, 2022Comments
-
DineshDB about 2 years
I have two tables
t1
andt2
. Both haveid
andname
columns. The name column oft1
is defined as not null and it has the default value of 'Peter'.I want to insert all the values from
t2
into myt1
table. But I have some null values int2
table. When I try to insert the values:Insert into t1 select * from t2;
It throws this error:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Name', table 'T1'; column does not allow nulls.Is there any possibilities to set the default value to the column when we try to
insert
thenull
value. -
jakubiszon over 3 yearsI am afraid the third solution fails if your column type is
uniqueidentifier
and the default is(newid())
. It actually fails for all function calls in theCOLUMN_DEFAULT
regardless of datatype.