Tech Talk

LIGHTLY TECHNICAL OBSERVATIONS ON PEOPLESOFT, SQR, AND RELATED AND UNRELATED TOPICS

Archives

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   || ''');'
   FROM  PS_PERSONAL_DATA
   WHERE ...

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 them.

Why couldn't we just create an INSERT INTO...SELECT FROM to accomplish the same goal?

   INSERT INTO PS_MYTABLE
   (SELECT EMPLID
         , NAME
    FROM   PS_PERSONAL_DATA
    WHERE  ...
   )

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:

   if ...
     let $Table = 'PS_PERSONAL_DATA'
   else
     let $Table = ...
   end-if
   if ...
     let $WhereBU = 'AND BUSINESS_UNIT = ' || ...
   else
     let $WhereBU = ' '
   end-if
   begin-select
EMPLID
       do Something
     FROM  [$Table]
     WHERE COUNTRY = 'USA'
     [$WhereBU]
   end-select

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.