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:


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


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)

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.

  • 5
    What is the context for this? you should be using parameterised queries if this is going anywhere near production. Sep 25, 2017 at 18:26
  • 2
    Do you need insert into nulos (nome,cidade) values (Juliana,NULL) in the result? Or insert into nulos (nome,cidade) values ('Juliana',NULL)? Sep 25, 2017 at 18:27
  • 1
    Why are you constructing dynamic queries here? Please tell me this isn't user input.
    – jpmc26
    Sep 25, 2017 at 22:15

1 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)
  • 4
    @RacerSQL as long as none of your names are O'Brien Or Robert'); DROP TABLE Students;-- Sep 25, 2017 at 18:33
  • 1
    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.
    – indiri
    Sep 25, 2017 at 18:43
  • 1
    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. Sep 25, 2017 at 18:46
  • Oh yeah I've added the replace for it. Thanks both of you
    – Racer SQL
    Sep 25, 2017 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.