Create Insert query with null value in SQL Server

insert, null, sql, sql-server-2008

Solution

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

Problem

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

Original source