I would like to know the process of exporting Insert statements of multiple queries at a time.
I generally run 10 queries and copy the insert statement for each query separately by executing all the queries one by one. But since it is time consuming, I would like to know whether there is any process available to export insert statements from multiple queries at a time into notepad.
Kamjuri
There are probably other ways of accomplishing this. But this is how I have done it the past.
I first set sqlplus session parameters so sqlplus brings nothing back except the rows or in your case generated insert statements.
I then define the spool file to which the results will be written.
Enter as many sql statements as needed with the Spool off after the final one.
I then run in Toad "as script’.
I hope this helps
set set echo off
set trimspool on
set trim on
set wrap on
set feedback off
set termout off
set head off
set pagesize 0
set linesize 1000
set newpage none
spool C:\all-my-sql-statements.sql
Select ‘insert into tablea…’ From sourcetab1;
Select ‘insert into tableb…’ From sourcetab2;
spool off
Hi Joe,
Thanks for the response.
Can you please explain in detail. I tried executing the way you have mentioned, however I am getting output of select statements into spool file. But that is not what I am looking for.
EG: I have to run below queries initially -
select * from abc where user_name = ‘abc’; // out put data will be more than 1 row >> so I get more than 1 insert statement for each select query if I save as insert statement on data grid.
select * from def where user_name = ‘abc’;
. . . . . . .
Similarly I run almost 10 queries for each request, and once I see out put data of each query, then I Rt.Click on data grid and click on save as insert statement > clip board > open notepad > paste the out put for each query separately.
But instead of doing this, If I run all the select queries as a script, all the insert statements related to those select queries should be copied into spool file instead of actual output of select query.
Any help to do this would be appreciated.
Thanks
Kamjuri,
I am old school so when generating dynamic sql I don’t bother using the Toad function that you mention.
The only way I know how do what you want for multiple tables is to generate the insert statements as a literal like below.
It is a pain to get the quotes to line up around character values/fields like employee_id or employee_status shown below.
But once you get them right you can put as many inserts statements to as many tables as you like.r even
select ‘insert into empbysit (EMPLOYEE_ID, SITE_NO, EMPLOYEE_STATUS) VALUES (’’’ || employee_id || ‘’’,’ ||
site_no || ',' || '''A'');'
from empbysit
where employee_id = ‘9063’;
If you don’t care to see the output you can use the PL/SQL “execute immediate” command and the insert statements execute and never hit the ground.
I hope this helps
Joe,
Thanks for taking time out. But I assume that you understood my requirement in a different way.
I have to delete selected data from all the related tables from schema - 10 Tables in this case has related data (If i have to delete one employee data, it needs to be deleted from all the 10 tables).
Lets say if i run below query (my requirement is to delete the rows matching my below criteria and before doing that i have to take backup using insert statement because if some thing goes wrong, i can always run the insert statements again to keep the data back which has been deleted)
select * from employees where location =‘FL’;
output is:
Emp_id Emp_name location
001 abcd FL
002 efgh FL
What I am doing is using Toad tool inbuilt function > generating insert statement for the above two rows
insert into employees (emp_id,emp_name,location) values (001,‘abcd’,‘FL’);
insert into employees (emp_id,emp_name,location) values (002,‘efgh’,‘FL’);
Now i open a notepad and copy these two insert statements into it.
Again I run another query to delete data from other relevant table
select * from emp_data where location =‘FL’;
Emp_id fst_name lst_name dob doj sal location
001 abcd efgh 01/01/80 01/01/01 5000 FL
002 efgh ijkl 01/01/85 01/07/12 2500 FL
Again i will generate insert statements using toad insert statement function for the above two rows in data grid and copy those insert statements in the notepad.
and this process continues until i query all the 10 tables from which i need to delete the data and copy insert statements for those rows.
Since copying and pasting the insert statements using TOAD Tool insert statement functionality is manual process which i am doing for each query seperately. I would like to know whether this process can be automated and if i can execute all my select queries on a go, so that all the insert statements related to those select queries should be automatically copied to notepad instead of doing it manually one by one.
Please let me know if you understood my requirement.
Thanks
Yes I believe I understand you are running 10 separate statements and using the Toad function to convert select statements to save a backups to replace the data should you need to.
I am not sure if this function seemingly designed for the conversion a single data set into one that will fulfill your requirement of 10 statements.
That being said I created and populated the data for first two scripts to create your insert statements that you could spool to a file for backup. I
Unless you find a way to get the function to work like you want, I offer this as alternative.
If you decide to try to convert the others I suggest you add a field at a time(in the values) to make sure all the quoted character type fields have a single preceding and trailing quote and without messing up prior ones. It can be a bit tricky.
Good luck
select ‘insert into employees (emp_id, emp_name, location) values (’ ||
emp_id || ‘,’’’ || emp_name || ‘’’,’’’ || location || ‘’’);’
from employees
where location = ‘FL’;
OUTPUT: This matches your example and inserted without issue.
insert into employees (emp_id, emp_name, location) values (001,‘abcd’,‘FL’);
insert into employees (emp_id, emp_name, location) values (002,‘efgh’,‘FL’);
select ‘insert into emp_data (emp_id, fst_name, lst_name, dob, doj, sal, location) values (’ ||
emp_id || ‘,’’’ || fst_name || ‘’’,’’’ || lst_name || ‘’’, to_date(’’’ || to_char(dob, ‘MM/DD/YYYY’) ||
‘’’,’‘MM/DD/YYYY’’), to_date(’’’ || to_char(doj, ‘MM/DD/YYYY’) || ‘’’,’‘MM/DD/YYYY’’), ’ || sal || ‘, ‘’’ || location || ‘’’);’
from emp_data
where location = ‘FL’;
OUTPUT: inserted without issue
insert into emp_data (emp_id, fst_name, lst_name, dob, doj, sal, location) values (001,‘abcd’,‘efgh’, to_date(‘01/01/1980’,‘MM/DD/YYYY’), to_date(‘01/01/2001’,‘MM/DD/YYYY’), 5000, ‘FL’);
insert into emp_data (emp_id, fst_name, lst_name, dob, doj, sal, location) values (002,‘efgh’,‘ijcl’, to_date(‘01/01/1985’,‘MM/DD/YYYY’), to_date(‘01/07/2012’,‘MM/DD/YYYY’), 2500, ‘FL’);
I think that Joe’s suggestions are about as good as you can get. You have a very specific need and you’ll be hard pressed to find a tool that has a tailored solution for your unique requirements already built in.
If your backup and delete procedure is largely the same over and over again create a script using Joe’s recommendations and use variables for the values that change. You can then save the script and call on it when needed. I have all sorts of custom problems here at my work and I have loads of bat files to address them. Your requirements are no different. Setup a process so that you can largely reuse it with minimal effort going forward.