how to insert default values in fields if select query returns null in sql server

Question

Hi All,

Can someone suggest how to insert default values in Columns when the select query returns NULL Values?

Currently I'm using the below script:

INSERT INTO [dbo].[Test] ([time1])
SELECT isnull(c.[time2],'1753-01-01 00:00:00.000') from [dbo].[Test1] c

where [time2] allows Null values and [time1] does not allow Null values.

but I have 100's of fields and multiple tables which is taking too much off time.

Thanks in Advance,
Srinivas Maddula.

Answer 1

Run a null check using a COALESCE. COALESCE returns the first non null argument it is provided with.

INSERT INTO [dbo].[Test] ([time1])
SELECT COALESCE(c.[time2],'1753-01-01 00:00:00.000') from [dbo].[Test1] c

Answer 2

There is no silver bullet here. If you don't fancy writing the statement manually, you can generate it. Then again, you may face a column which does not permit NULL values, but nor has a default value set up.

Maybe you need to take a deeper look of what you are actually doing?

Answer 3

Hi @Srinivas Maddula ,

You could have a try with dynamic statement to generate this statement automatically if you have some programming skills of T-SQL.

Please refer below example which insert values of multiple columns from test1 table to test table and check whether it is working.

create table [Test]  
 (id int identity(1,1),  
 [time1] datetime not null,  
 [time12] datetime not null,  
 [time123] datetime not null)  
  
 create table [Test1]  
 (id int,  
 [time2] datetime,  
 [time3] datetime,  
 [time4] datetime)  
      
 insert into [Test1] values  
 (1,NULL,NULL,NULL)  
  
 DECLARE @TABLENAME VARCHAR(100)='TEST1' --the name of source table  
 DECLARE @SCHEMA VARCHAR(100)='DBO'--the name of  schema  
 DECLARE @SQL NVARCHAR(MAX)=''  
 DECLARE @S NVARCHAR(MAX)=''  
  
 SELECT @S=STUFF(( SELECT ', isnull('+QUOTENAME(COLUMN_NAME)+',''1753-01-01 00:00:00.000'')'   
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE IS_NULLABLE = 'YES' AND TABLE_NAME=@TABLENAME AND TABLE_SCHEMA=@SCHEMA AND DATA_TYPE='datetime'  
 FOR XML PATH('') ), 1, 1, '')  
  
 --list all the necessary column names here  
SET @SQL=' INSERT INTO [dbo].[Test] ([time1],[time12],[time123])   
 SELECT '+@S+ ' from '+ @SCHEMA+'.'+@TABLENAME  
  
EXECUTE sp_executesql  @SQL  
  
select * from Test  

Output:

id	time1	time12	time123  
1	1753-01-01 00:00:00.000	1753-01-01 00:00:00.000	1753-01-01 00:00:00.000  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

There is not a way to use "DEFAULT" when using a select statement for values. You would have to use dynamic SQL to generate actual "INSERT .... ('xxx',DEFAULT,DEFAULT,'yyyyy')" to make it work.

Answer 5

Hi @Srinivas Maddula ,

Thanks for your update.

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data. We also need to see the expected result of the sample after inserting.

Please refer below example with more datatypes in both tables:

drop table if exists [Test],[Test1]  
  
create table [Test]  
(id int not null,  
[time1] datetime not null,  
[time12] datetime not null,  
col1 varchar(20) not null,  
col2 float not null,  
[time123] datetime not null);  
      
create table [Test1]  
(id int,  
[time2] datetime,  
col1 varchar(20),  
col2 float,  
[time3] datetime,  
[time4] datetime);  
          
insert into [Test1] values  
(1,NULL,NULL,NULL,NULL,NULL);  
      
DECLARE @TABLENAME VARCHAR(100)='TEST1' --the name of source table  
DECLARE @SCHEMA VARCHAR(100)='DBO'--the name of  schema  
DECLARE @SQL NVARCHAR(MAX)=''  
DECLARE @S NVARCHAR(MAX)=''  

--define the default value of more datatypes here     
SELECT @S=STUFF(( SELECT ', isnull('+QUOTENAME(COLUMN_NAME)+','+  
case when DATA_TYPE='datetime'  then '''1753-01-01 00:00:00.000'')'   
when DATA_TYPE='varchar' then ''''')'  
when DATA_TYPE='int' then '0)'  
when DATA_TYPE='float' then '0.0)'  
end  
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE IS_NULLABLE = 'YES' AND TABLE_NAME=@TABLENAME AND TABLE_SCHEMA=@SCHEMA  
FOR XML PATH('') ), 1, 1, '')  
      
--list all the necessary column names of Test table here  
SET @SQL=' INSERT INTO [dbo].[Test] (id,[time1],col1,col2,[time12],[time123])   
SELECT '+@S+ ' from '+ @SCHEMA+'.'+@TABLENAME  
  
EXECUTE sp_executesql  @SQL  
      
select * from Test  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.