|Generating SQL with SQL||KEVIN RESCHENBERG|
Last week's topic covered creating SQL using PeopleSoft
Query and Excel. Today we will create SQL using SQL.
This method can be used when most or all of the information
you need is contained within tables in the database, rather
than in a spreadsheet or text file. The general form is:
SELECT 'INSERT INTO PS_MYTABLE '
|| 'VALUES ('
|| '''' || EMPLID || ''','
|| '''' || NAME || ''');'
Run this SQL within your SQL tool (Query Analyzer, Toad,
or similar) and the result will be a list of SQL statements.
Copy these statements right back into the tool and execute
Why couldn't we just create an INSERT INTO...SELECT FROM to accomplish
the same goal?
INSERT INTO PS_MYTABLE
This is much simpler and would be preferred in most cases.
Here are some of the situations in which you might want to
generate the SQL instead:
- You want a script that can be run in multiple environments,
including databases that do not include the source data.
- You want to "freeze" the results so that each time you
run the script, the same data is used.
- Not all of the data is available within tables and you
need to adjust the resulting script before running it.
- You are using Oracle and the INSERT requires many
"= (SELECT ...)" clauses with complex WHERE conditions.
(This should not be an issue under SQL Server.)
- The data you are using as your source data is actually
a list of tables from which you want to SELECT.
Yet another way to create SQL is with SQR, either by writing
a script to a flat file, or by building dynamic SQL:
let $Table = 'PS_PERSONAL_DATA'
let $Table = ...
let $WhereBU = 'AND BUSINESS_UNIT = ' || ...
let $WhereBU = ' '
WHERE COUNTRY = 'USA'
Next week I'll show a specific useful application of generating SQL
with SQL: Scanning many tables to find a particular string
in the database.