CODEWITHSUNDEEP
sqlsql-server-2008insertnull
Duk
Duk

Reputation: 927

Create Insert query with null value in SQL Server

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..

Upvotes: 4

Views: 47392

Answers (2)

Pirion
Pirion

Reputation: 519

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

Upvotes: 2

marc_s
marc_s

Reputation: 755421

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 ....

Upvotes: 2

Related Questions