Grigoriy Didorenko Grigoriy Didorenko - 7 months ago 14
SQL Question

Oracle Select which produce Insert into

I have query like

SELECT 'Insert INTO FOO(ID,NAME) values ('||id|| ',' ||NAME||');' as query
FROM FOO


This is just an example as I can't provide real query example. So, DON'T pay attention how it works just believe that it works as it should.

Question:

If NULL values exists in selected rows it just produces '' in INSERT INTO query. So I am getting an error while trying to execute insert query. How can I force SELECT query to return NULL instead of ''?

Example:

I have

Insert INTO FOO(ID,NAME) values (12,);


I want to have

Insert INTO FOO(ID,NAME) values (12,NULL);

Answer

I am guessing that NAME is a string, so it should be surrounded by single quotes. So, this might be the query you want:

SELECT 'Insert INTO FOO(ID,NAME) values (' || id || ', ''' || NAME ||''');' as query
FROM FOO;

If the issue is id, then you can do:

SELECT 'Insert INTO FOO(ID,NAME) values (' || (CASE WHEN id IS NULL THEN 'NULL' ELSE CAST(id AS VARCHAR2(255)) END) ||
       ', ''' || NAME ||''');' as query
FROM FOO;