Toad World® Forums

how-to question...moving data from a test instance to production

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 :wink:

Roger S.