DineshDB DineshDB - 7 months ago 6
SQL Question

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

I have two tables

t1
and
t2
. Both has
id
and
name
columns. The name column of t1 is 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.

Help me to get this, thanks in advice.

Answer

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

Third Solution :(Best)

    Declare @GetDefaultValue varchar(255)

    SELECT  @GetDefaultValue= COLUMN_DEFAULT
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
      AND TABLE_NAME = 'T1'
      AND COLUMN_NAME = 'name'


 insert into t1 select id,isnull(name,@GetDefaultValue) from t2