Generate a script out of nullable column in SQL table [duplicate]

Question

Following is my query:

SELECT 'INSERT INTO MyTable (
CancellationReason
)
VALUES ( 
''' + rc.CancelReason  + '''
)'
FROM AnotherTable a

I get below result:

Since the column a.CancelReason is nullable, you see NULL values in the result set. But I need the result something like this (I need a string representation in place of NULL (basically an insert statement in the current example)):

I tried something like this but no help:

SELECT 'INSERT INTO MyTable (
CancellationReason
)
VALUES ( 
''' + ISNULL(a.CancelReason, null)  + '''
)'
FROM AnotherTable a

Could you please suggest?

Please update the tags in your question with the RDBMS you are using (postgres, mysql, sql server, snowflake, db2, etc). Likely you just need ISNULL(a.CancelReason, 'null') (notice that null is a string-literal in this case, not a literal null. — JNevill
– JNevill, Commented Mar 19 at 14:35

Harsh Varde · Accepted Answer · 2025-03-19 15:09:50Z

2

You can query like this w/o using CASE condition:

SELECT 'INSERT INTO MyTable (CancellationReason) VALUES (' + COALESCE('''' + CancelReason + '''','NULL')  + ')'
FROM AnotherTable a

or you can use direct INSERT INTO...SELECT statement as suggested by @Tim

answered Mar 19 at 15:09

Harsh Varde

3035 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raghavendra HG Mar 20 at 7:49

Hi Harsh, thanks for the reply. This seems to be simple one :) Thank you again!

Harsh Varde Mar 20 at 11:05

Happy to help you @RaghavendraHG :)

Tim Biegeleisen · Accepted Answer · 2025-03-19 14:37:57Z

1

Use an outer CASE expression to decide whether to insert null or a single quoted string:

SELECT 
    CASE WHEN rc.CancelReason IS NOT NULL
         THEN 'INSERT INTO MyTable (CancellationReason) VALUES (''' + rc.CancelReason + ''')'
         ELSE 'INSERT INTO MyTable (CancellationReason) VALUES (NULL)' END
FROM AnotherTable a;

But, more generally, you might have an easier time just executing a direct INSERT INTO...SELECT, along these lines:

INSERT INTO MyTable (CancellationReason)
SELECT CancelReason
FROM SomeOtherTable;

answered Mar 19 at 14:37

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

Raghavendra HG Mar 20 at 7:50

Hi Tim, this helped. Thank you :)

Collectives™ on Stack Overflow

Generate a script out of nullable column in SQL table [duplicate]

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related