Quick data insert into a table

This SQL*Plus script with all descriptions and documentation embedded within.

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 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 last published in May 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close