Generate multiple statements

[jjlbmcnews]

Hi,

I need to issue a number of insert statements and I know theres a way to generate all the statements dynamically so that I don't have to type each statement but I can't for the life of me remember how to do it. I know its something like select 'abc' = 'insert into table1 (field1, field2) values ('|| column from table2 ||', 'value2')
from table2 or something like that.

 

[lewisp]

Do you want to generate a script that contains multiple insert statements, or write a single insert that creates multiple rows?

If you mean the latter, use something like this:

INSERT INTO your_target_table (
  column1,
  column2,
  etc )
SELECT tab.col1,
       tab.col2,
       tab.etc
FROM   your_source_table tab
WHERE  etc;

Otherwise, you have to generate a script that you subsequently run to create your data;

SET HEADING OFF
SET FEEEDBACK OFF
SET PAGES 0

SELECT 'INSERT INTO your_target_table (columns...) ' ||
        'VALUES ( ' ||
      '''' || char_or_date_column || ''',' ||
      numeric_column || ',' ||
      '''' || another_char_column || ''')'
FROM   your_source_table

SPOOL runit.sql
/
SPOOL OFF

@runit