generic sql script for dml

Question

i have a table test which contains all the table names , column_names and flag . i want to create the generic script for "insert into test" from all tables present in the current schema with the flg logic as mentioned below : flg should be 'Y' for all the columns before filler1 else 'N' currently i am hardcoding as 'N'.

drop table test;
CREATE TABLE TEST (TABLE_NM VARCHAR2(10) ,COL_NM VARCHAR2(10) ,  FLG CHAR(1));

CREATE TABLE XYZ (FNAME VARCHAR2(10) , LNAME VARCHAR2(10) , PID NUMBER , FILLER1 VARCHAR2(10) , FILLER2 VARCHAR2(10) );

SELECT TABLE_NAME , COLUMN_NAME
from ALL_TAB_COLUMNS
WHERE table_name='XYZ'
order by COLUMN_ID;

script query :

 select 'INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('''||TABLE_NAME||
    ''','''||COLUMN_NAME||''',''N'''||')'
    from ALL_TAB_COLUMNS
    where table_name='XYZ'
    order by COLUMN_ID;

Expected output

 INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','FNAME','Y');
    INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','LNAME','Y');
    insert into TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','PID','Y');
    INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','FILLER1','N');
    insert into TEST (TABLE_NM,COL_NM,FLG)VALUES ('XYZ','FILLER2','N');

how can i achieve this? Thanks in advance.

Answer 1

If you got lost in all those single quotes, I'd suggest you to use CHR(39) (which is a single quote) instead.

Here's an example.

SQL> create table test (table_nm varchar2(10),
  2                     col_nm   varchar2(10),
  3                     flg      char(1));

Table created.

SQL> create table xyz (fname     varchar2(10),
  2                    lname     varchar2(10),
  3                    pid       number,
  4                    filler1   varchar2(10),
  5                    filler2   varchar2(10),
  6                    some_col  number);

Table created.

CTE FILLER1_POS returns position (i.e. column_id) of the FILLER1 column; it is the used in CASE to distinguish columns whose ID is lower (or higher) that FILLER1 column's.

SQL> with filler1_pos as
  2    (select column_id fid_pos
  3     from all_tab_columns
  4     where table_name = 'XYZ'
  5       and column_name = 'FILLER1'
  6    )
  7  select 'insert into test (table_nm, col_nm, flg) values (' ||
  8     chr(39) || a.table_name  || chr(39) ||', '||
  9     chr(39) || a.column_name || chr(39) ||', '||
 10     chr(39) || case when a.column_id < f.fid_pos then 'Y'
 11                     else 'N'
 12                end           || chr(39) ||');' result
 13    from all_tab_columns a join filler1_pos f on 1 = 1
 14    where a.table_name = 'XYZ'
 15    order by a.column_id;

RESULT
--------------------------------------------------------------------------------
insert into test (table_nm, col_nm, flg) values ('XYZ', 'FNAME', 'Y');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'LNAME', 'Y');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'PID', 'Y');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'FILLER1', 'N');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'FILLER2', 'N');
insert into test (table_nm, col_nm, flg) values ('XYZ', 'SOME_COL', 'N');

6 rows selected.

SQL>

Let's run those INSERTS and check the result:

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'FNAME', 'Y');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'LNAME', 'Y');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'PID', 'Y');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'FILLER1', 'N');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'FILLER2', 'N');

1 row created.

SQL> insert into test (table_nm, col_nm, flg) values ('XYZ', 'SOME_COL', 'N');

1 row created.

SQL> select * from test;

TABLE_NM   COL_NM     F
---------- ---------- -
XYZ        FNAME      Y
XYZ        LNAME      Y
XYZ        PID        Y
XYZ        FILLER1    N
XYZ        FILLER2    N
XYZ        SOME_COL   N

6 rows selected.

SQL>

Answer 2

One option might be using decode with sign as :

SELECT 'INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES ('''||TABLE_NAME||
    ''','''||COLUMN_NAME||''','''||decode(sign(u.column_id-3),1,'N','Y')||''');'
  FROM USER_TAB_COLUMNS U
 WHERE table_name='XYZ'
 ORDER BY COLUMN_ID;

SQL Fiddle Demo

Answer 3

Does this do what you want?

SELECT 'INSERT INTO TEST (TABLE_NM,COL_NM,FLG)VALUES (''' ||
       TABLE_NAME ||
       ''',''' ||
       COLUMN_NAME ||
       ''',''' ||
       CASE WHEN SUBSTR(COLUMN_NAME,1,6) = 'FILLER' THEN 'N' ELSE 'Y' END ||
       ''');'        
  FROM ALL_TAB_COLUMNS
 WHERE table_name='XYZ'
 ORDER BY COLUMN_ID;