Concatenate with NULL values ( Should not replace NULL )

Question

After some time searching for an answer, I got a LOT of question saying to use coalesce,concat(), and etc, but what I need is:

Table:

create table nulos ( nome varchar(max))
alter table nulos add cidade varchar(max)

values:

insert into nulos values ( 'rafael','são paulo'),('juliana',null)

I need to concatenate the values inside this table, to a insert, like this:

 select 'insert into nulos (nome,cidade) values ('+nome+','+cidade+')'
from nulos

And the result of the select:

insert into nulos (nome,cidade) values (rafael,são paulo)
NULL

how can I use the NULL value inside this concatenation? every answer says something to replace the null with '' or '_', But what I need it this:

insert into nulos (nome,cidade) values (rafael,são paulo)
insert into nulos (nome,cidade) values (Juliana,NULL)

SET CONCAT_NULL_YIELDS_NULL on is not an option because it just deletes the NULL value, and I need to insert it.

• Martin Smith: What is the context for this? you should be using parameterised queries if this is going anywhere near production.
• ypercubeᵀᴹ: Do you need insert into nulos (nome,cidade) values (Juliana,NULL) in the result? Or insert into nulos (nome,cidade) values ('Juliana',NULL)?
• jpmc26: Why are you constructing dynamic queries here? Please tell me this isn't user input.

Answer 1

Wrap your strings in single quotes so they will be input as strings. The NULL does not need quotes.

SELECT 'INSERT INTO nulos (nome,cidade) VALUES (' +
 CASE WHEN nome is null then 'NULL' ELSE ''''+
 REPLACE(nome,'''','''''')+'''' END + 
 ',' +
 CASE WHEN cidade is null then 'NULL' ELSE ''''+
 REPLACE(cidade,'''','''''')+'''' END +
 ')'
FROM nulos

This will give you:

INSERT INTO nulos (nome,cidade) VALUES ('rafael','são paulo')
INSERT INTO nulos (nome,cidade) VALUES ('Juliana',NULL)

• Martin Smith: @RacerSQL as long as none of your names are O'Brien Or Robert'); DROP TABLE Students;--
• indiri: I added escaping for O'Brien. As far as the ; I didn't code for that because it wasn't part of the question but I agree that would be a whole other problem. I only do this (above) when scripting inserts from already cleaned data but yeah, it should not be used for unprotected inputs like front end apps.
• Martin Smith: As long as the quote is escaped correctly the second one (bobby tables) won't actually cause a problem either. The problem is the unescaped quote closes off the string literal there too. There are potential problems with homoglyph smuggling but not as written here.
• Racer SQL: Oh yeah I've added the replace for it. Thanks both of you