Concatenate with NULL values ( Should not replace NULL ) 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.
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)