Recipe5.6.Using SQL to Generate SQL

Recipe 5.6. Using SQL to Generate SQL

Problem

You want to create dynamic SQL statements, perhaps to automate maintenance tasks. You want to accomplish three tasks in particular: count the number of rows in your tables, disable foreign key constraints defined on your tables, and generate insert scripts from the data in your tables.

Solution

The concept is to use strings to build SQL statements, and the values that need to be filled in (such as the object name the command acts upon) will be supplied by data from the tables you are selecting from. Keep in mind, the queries only generate the statements; you must then run these statements via script, manually, or however you execute your SQL statements. The examples below are queries that would work on an Oracle system. For other RDBMSs the technique is exactly the same, the only difference being things like the names of the data dictionary tables and date formatting. The output shown from the queries below are a portion of the rows returned from an instance of Oracle on my laptop. Your result sets will of course vary.

 /* generate SQL to count all the rows in all your tables */  select 'select count(*) from '||table_name||';' cnts   from user_tables; CNTS ---------------------------------------- select count(*) from ANT; select count(*) from BONUS; select count(*) from DEMO1; select count(*) from DEMO2; select count(*) from DEPT; select count(*) from DUMMY; select count(*) from EMP; select count(*) from EMP_SALES; select count(*) from EMP_SCORE; select count(*) from PROFESSOR; select count(*) from T; select count(*) from T1; select count(*) from T2; select count(*) from T3; select count(*) from TEACH; select count(*) from TEST; select count(*) from TRX_LOG; select count(*) from X; /* disable foreign keys from all tables */  select 'alter table '||table_name||        ' disable constraint '||constraint_name||';' cons   from user_constraints  where constraint_type = 'R'; CONS ------------------------------------------------ alter table ANT disable constraint ANT_FK; alter table BONUS disable constraint BONUS_FK; alter table DEMO1 disable constraint DEMO1_FK; alter table DEMO2 disable constraint DEMO2_FK; alter table DEPT disable constraint DEPT_FK; alter table DUMMY disable constraint DUMMY_FK; alter table EMP disable constraint EMP_FK; alter table EMP_SALES disable constraint EMP_SALES_FK; alter table EMP_SCORE disable constraint EMP_SCORE_FK; alter table PROFESSOR disable constraint PROFESSOR_FK; /* generate an insert script from some columns in table EMP */  select 'insert into emp(empno,ename,hiredate) '||chr(10)||        'values( '||empno||','||''''||ename        ||''',to_date('||''''||hiredate||''') );' inserts   from emp  where deptno = 10; INSERTS -------------------------------------------------- insert into emp(empno,ename,hiredate) values( 7782,'CLARK',to_date('09-JUN-1981 00:00:00') ); insert into emp(empno,ename,hiredate) values( 7839,'KING',to_date('17-NOV-1981 00:00:00') ); insert into emp(empno,ename,hiredate) values( 7934,'MILLER',to_date('23-JAN-1982 00:00:00') ); 

Discussion

Using SQL to generate SQL is particularly useful for creating portable scripts such as you might use when testing on multiple environments. Additionally, as can be seen by the examples above, using SQL to generate SQL is useful for performing batch maintenance, and for easily finding out information about multiple objects in one go. Generating SQL with SQL is an extremely simple operation, and the more you experiment with it the easier it will become. The examples provided should give you a nice base on how to build your own "dynamic" SQL scripts because, quite frankly, there's not much to it. Work on it and you'll get it.