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.
Best Answer
Wrap your strings in single quotes so they will be input as strings. The NULL does not need quotes.
This will give you: