Create Insert query with null value in SQL Server

Question

I create insert query for Organization table.

select 'Insert into Organizations(Name, IndustryId, ContactPerson, Email, Website, LocationId, ContactNumber, Mobilenumber) values(''' + 
       IsNull(Nameofthecompany, 'NULL') + ''',' + 
       Isnull(IndustryType, 'NULL') + ',''' +
       Isnull(Nameofthepersonresponsibleforrecruitment, 'NULL') + ''', ''' +
       Isnull(EmailId, 'NULL') + ''', ''' +
       Isnull(websiteaddress, 'NULL') + ''',' +
       Isnull(Location, 'NULL') + ',' +
       Isnull(PhoneNumber, 'NULL') + ',' +
       Isnull(MobileNumber, 'NULL') + ')' 
from Organization

Here I have the result set

Insert into Organizations(Name, IndustryId, ContactPerson, Email, Website, LocationId, ContactNumber, Mobilenumber)
values('username', industry, 'Name', 'NULL', 'NULL', place, NULL, 999999999)

I don't want the NULL value within quotes. If I remove the quotes means I get error. Please Help me find out the problem..

Well, what else do you want to have instead of 'NULL' ? Just replace those with whatever you want to add ..... — marc_s
– marc_s, Commented Apr 10, 2013 at 5:00
Show me your column definition. You can only insert Null if your column allow Null — Doan Cuong
– Doan Cuong, Commented Apr 10, 2013 at 5:01

Pirion · Accepted Answer · 2013-04-10 05:49:56Z

2

If a value is NULL, then adding it to a string will produce a NULL. This allows us to add the quotes in the ISNULL check and just produce NULL in the true value of the check, producing the correct syntax for nulls or not nulls as necessary.

select 'Insert into Organizations(Name, IndustryId, ContactPerson, Email, Website, LocationId, ContactNumber, Mobilenumber) values(' + 
       IsNull(''''+Nameofthecompany+'''', 'NULL') + ', ' + 
       Isnull(''''+IndustryType+'''', 'NULL') + ', ' +
       Isnull(''''+Nameofthepersonresponsibleforrecruitment+'''', 'NULL') + ', ' +
       Isnull(''''+EmailId+'''', 'NULL') + ', ' +
       Isnull(''''+websiteaddress+'''', 'NULL') + ', ' +
       Isnull(''''+Location+'''', 'NULL') + ', ' +
       Isnull(PhoneNumber, 'NULL') + ', ' +
       Isnull(MobileNumber, 'NULL') + ')' 
from Organization

edited Apr 10, 2013 at 5:49

answered Apr 10, 2013 at 5:42

Pirion

5192 silver badges6 bronze badges

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

Comments

marc_s · Accepted Answer · 2013-04-10 05:43:12Z

2

If you want to use NULL (as a literal - not a string) for your NULL values, then the creation of the INSERT statement gets a lot more complicated; if the value is NULL, then you need to add the literal NULL without a leading and trailing '.

For each column where you want to do this, you'll need to use a CASE statement - something like this:

select 'INSERT INTO Organizations(.....) ' + 
       'VALUES(' + 
       CASE 
           WHEN NameOfTheCompany IS NOT NULL 
              THEN '''' + NameOfTheCompany + ''', '
              ELSE 'NULL, ' 
       END + 
       CASE 
           WHEN IndustryType IS NOT NULL 
              THEN '''' + IndustryType + ''', '
              ELSE 'NULL, ' 
       END +
       ..... and so on ......
       + ')'

... and so on, for each column you need this CASE statement ....

answered Apr 10, 2013 at 5:43

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

2 Comments

JonnyRaa Over a year ago

bizarrely this seems to be the only bit of information about how to insert null deliberately I could find online! Suppose it's just assumed knowledge

donohealy Oct 10 at 15:16

The solution with the case statements is useful where some of the column values are null and others contain data. If the entire column is null, then simply omit that column from the insert and the values will remain null.

Collectives™ on Stack Overflow

Create Insert query with null value in SQL Server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related