question

SrinivasMaddula-4047 avatar image
0 Votes"
SrinivasMaddula-4047 asked MelissaMa-msft commented

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

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.

sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


What does it take time: writing statements or executing statements?


0 Votes 0 ·

Not clear to your question. For the multiple nullable columns, NULL in each column may be in different rows, i.e., data in the col1 is NULL at the first row, but data in the col2 may be at the second row, .... And also what does "Default values" mean?

0 Votes 0 ·

Hi @SrinivasMaddula-4047,

Could you please provide any update?

Thanks.

Best regards,
Melissa

0 Votes 0 ·
dwakel avatar image
0 Votes"
dwakel answered SrinivasMaddula-4047 commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @dwakel,

I just want to insert the default value, not the first non-null value.

But the problem is I have few 100 columns in this table and like this, I have 10-15 tables.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered SrinivasMaddula-4047 edited

Hi @SrinivasMaddula-4047,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for the Answer...

What if we have multiple datatypes in the same table.

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @SrinivasMaddula-4047,

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

118066-output.png

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.


output.png (2.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.