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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close