Geek Answers Handbook

How to set a default value when inserting a null value in a non-empty SQL Server column?

I have two tables t1and t2. Both have columns idand name. The name column is t1defined as non-null and defaults to Peter.

I want to insert all the values ​​from t2into a table t1. But I have some null values ​​in the table t2. When I try to insert values:

Insert into t1 
   select * 
   from t2;

It throws this error:

Msg 515, Level 16, State 2, Line 1
Cannot insert a NULL value in the Name column, table T1; column does not allow zeros.

Is it possible to set a default value for a column when trying a insertvalue null.

+4
source share
4 answers

First decision

   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
+6
source

So instead

Insert into t1 select * from t2

you can rewrite your request as

Insert into t1 
select col1,col2, ISNULL(name, 'Peter'), othercolumns from t2
+2
source

COALESCE

Query

INSERT INTO t1(Id, Name)
SELECT Id, COALESCE(Name, 'Peter') FROM t2;

CASE.

Query

INSERT INTO t1(Id, Name)
SELECT Id, CASE WHEN Name IS NULL THEN 'Peter' ELSE Name END
FROM t2;
+2

:

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

.

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

+1

More articles:


All Articles