Tip

Quick data insert into a table

This is an Oracle SQL*Plus script, with all descriptions and documentation embedded within it:

-- INSERT.SQL
--
-- A method of Quickly inserting Data into a Table
--
-- Peter Robson, April

    Requires Free Membership to View

2001. -- -- Script to enable quick insert of data / rows into -- any table, without having to construct the SQL -- 'insert' clause. Ideal if you want to insert a -- few rows into a table without having to check -- the attribute definitions. -- -- User is prompted for name of the table into which -- data is to be inserted. This script then uses the -- Oracle data dictionary to construct the insert -- script, which is placed into a temporary script -- file. It then runs that script file, which prompts -- the user to input data for each attribute in turn. -- -- Additional rows can be inserted by re-running -- the insert script, which is of course already -- in the buffer, ie '/' -- -- Notice that ALL character fields are converted -- to upper case. You may modify this if required. -- -- This script makes use of some of the SQL*Plus -- code generation features that I presented at -- UK Oracle User Group Annual Conference, 2000. -- Further examples can be seen at -- http://peter-robson.port5.com -- ------------------------------------------------- -- set echo off set sqlprompt "" set trims on set pagesize 0 set feedback off set verify off undefine table -- prompt Enter name of table to populate: accept table prompt ' Table: ' -- set termout off spool ins.sql select 'insert into &&table', ' (' from dual; select column_name||' ,' from user_tab_columns where table_name = upper('&&table') and column_id < ( select max(column_id) from user_tab_columns where table_name = upper('&&table')) order by column_id; -- 5: select column_name from user_tab_columns where column_id = ( select max(column_id) from user_tab_columns where table_name = upper('&&table')) and table_name = upper('&&table') ; -- 6: select ') values (' from dual; -- 7: select 'upper(''&'||column_name||''') ,' from user_tab_columns where table_name = upper('&&table') and column_id < ( select max(column_id) from user_tab_columns where table_name = upper('&&table')) order by column_id; -- 8: select 'upper(''&'||column_name||''') )'|| chr(10) || '/ ' from user_tab_columns where column_id = ( select max(column_id) from user_tab_columns where table_name = upper('&&table')) and table_name =upper('&&table') ; spool off undefine table -- 9: set sqlprompt "SQL>" set feedback on set pagesize 24 set trims on set termout on @ins.sql --

For More Information


This was first published in May 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.