CODEWITHSUNDEEP
sqlsql-server-2008insert
PoliDev
PoliDev

Reputation: 1468

struggling with creating Insert query

I create Insert statement for organization table like this:

select'Insert into Organizations(Name,ContactPerson,ContactNumber,Mobilenumber)values('''+Nameofthecompany+''+','+Nameofthepersonresponsibleforrecruitment+','+PhoneNumber+','+MobileNumber+''')' from Organization

When I execute this statement I get insert statement. But the issue is where the value is null, it shows all columns null.

Example: (in database)

so my insert statement looks like:

Null.

I want only that column provide null. other details showing properly. Is there any way in sql server? Help me anyone...

Upvotes: 1

Views: 52

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

The result of concatenating anything to NULL, even itself, is always NULL. Workaround with ISNULL function:

select'Insert into Organizations(Name,ContactPerson,ContactNumber,Mobilenumber)
values('''+ISNULL(Nameofthecompany, 'NULL')+''+','
          +ISNULL(Nameofthepersonresponsibleforrecruitment, 'NULL')+','
          +ISNULL(PhoneNumber, 'NULL')+','
          +ISNULL(MobileNumber, 'NULL')+''')' 
from Organization

Demo on SQLFiddle

Upvotes: 1

marc_s
marc_s

Reputation: 755381

Sure - just use ISNULL(..) to turn a NULL into e.g. an empty string:

SELECT 
    'INSERT INTO Organizations(Name, ContactPerson, ContactNumber, Mobilenumber) VALUES(''' + 
    ISNULL(Nameofthecompany, '') + '' + ',' + 
    ISNULL(Nameofthepersonresponsibleforrecruitment, '') + ',' + 
    ISNULL(PhoneNumber, '') + ',' + ISNULL(MobileNumber,'') + ''')' 
FROM Organization

Upvotes: 1

Chris Denning
Chris Denning

Reputation: 1042

When you are adding each of the parameters to the SQL statement, you need to check whether they're null, and if so use the keyword NULL, otherwise include a literal string surrounded with single quotes, but bearing in mind that if the string contains any single quotes, they need to be replaced with two single quotes.

Update the SQL for each parameter something like the following:

CASE WHEN MobileNumber IS NULL THEN 'NULL' ELSE '''' + REPLACE(MobileNumber, '''', '''''') + '''' END

Upvotes: 0

Related Questions