How to set default value while insert null value into not null column SQL Server?

Question

I have two tables t1 and t2. Both have id and name columns. The name column of t1 is defined as not null and it has the default value of 'Peter'.

I want to insert all the values from t2 into my t1 table. But I have some null values in t2 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 the null value.

KumarHarsh · Accepted Answer · 2021-02-04 11:54:14Z

4

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

edited Feb 4, 2021 at 11:54

answered Apr 20, 2016 at 6:13

KumarHarsh

5,0941 gold badge22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jakubiszon Over a year ago

I 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 the COLUMN_DEFAULT regardless of datatype.

DhruvJoshi · Accepted Answer · 2016-04-20 06:10:08Z

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

answered Apr 20, 2016 at 6:10

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

Comments

Ullas · Accepted Answer · 2016-04-20 06:11:46Z

1

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;

answered Apr 20, 2016 at 6:11

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Comments

Mahesh Bhosale · Accepted Answer · 2016-04-20 06:16:41Z

0

Modify your query like:

Insert into t1 select COALESCE(column1,'') from t2;

For more details refer following link

http://www.w3schools.com/sql/sql_isnull.asp

answered Apr 20, 2016 at 6:16

Mahesh Bhosale

11211 bronze badges

Collectives™ on Stack Overflow

How to set default value while insert null value into not null column SQL Server?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related