1

I am trying to construct INSERT sentences from a query like below:

INSERT INTO MyTable (Field1, Field2) VALUES (value1,value2);

So I do:

SELECT CONCAT('INSERT INTO MyTable (Field1, Field2) VALUES (', value1, ',', value2, ');')
FROM OneTable

The problem I have is that sometimes, value1 may be NULL so in the result I need to obtain things like:

INSERT INTO MyTable (Field1, Field2) VALUES (NULL,5);

how can I do this?

Willy
  • 9,848
  • 22
  • 141
  • 284
  • http://stackoverflow.com/questions/5329542/php-mysql-insert-null-values – fizzi Dec 21 '16 at 09:45
  • Why not use sp_executesql with proper parameters? – Bridge Dec 21 '16 at 09:45
  • it looks like you are trying to `INSERT INTO MyTable (Field1, Field2) SELECT column1, Column2 FROM OneTable`. I don't understand why you want to concat this... as long as `Field2` is `NULLABLE`, you don't need `COALESCE` or `ISNULL` – S3S Dec 21 '16 at 15:28

1 Answers1

3

Change your value1 to this

Coalesce(value1, 'NULL')

But I think you should use sp_executesql and provide parameters for it.

Kim Hoang
  • 1,338
  • 9
  • 24
  • Is this approach preferable to using `ISNULL` and if so, why? Is there a functional difference between `COALESCE(value1,'NULL')` and `ISNULL(value1,'NULL')`? – 3N1GM4 Dec 21 '16 at 09:48
  • 2
    For that, you can refer to this thread http://stackoverflow.com/questions/18828641/sql-difference-between-coalesce-and-isnull – Kim Hoang Dec 21 '16 at 09:51