Hi, maybe someone can offer me a clever idea on how to do this… I
have a script that selects data from various tables in different schemas
based on a parameter I pass in. What I’d like to be able to do is to
run this script and have a script generated containing insert statements
for the data that is selected for each of the select statements in the
script . Then I could run this collection of inserts in my production
database to get it in synch with test. Right now I have to run each of
these many statements individually and export as insert
statements…(and there is a bug in TOAD so that only the first
statement will successfully pick up the parameter when I run them
individually…which means I have to hard code the selection parameter
for each…just to make the task a little more tedious) Any ideas?
Are you using double ampersand when you try to have parameter picked up multiple
times – as it is in sql plus and toad – or using single ampersand
Perhaps the schema compare functionality of Toad can help?
If so, it’d certainly be easier than building a script to dynamically
search and build the insert statements yourself.
However, if you must modify your current script, I imagine it shouldn’t be
too difficult to recode the collection of original inserts as strings instead.
Example;
Instead of:
Select column_1, column_2
From some_table
Where differences_exist;
You would have:
Select ‘insert into ‘ || some_table {assuming the table names
don’t change} || ‘ (column_1, column_2) values (‘ || column_1
{numeric value} || ‘, ‘’’ || column_2 {a character
value} || ‘’’);’
From some_table
Where differences_exist;
Caveat: I wrote the above in the email so it’s full of MS
“smart” quotes. The “wavy” brackets indicate my own
comments/assumptions. Additionally, while I’m pretty sure I have the quote
combinations correct, I offer no guarantee 
Roger S.