An alternative to SQL INSERT statements

A simple suite of (hidden) SQL scripts which enables the user to insert the odd few rows into any table in the users schema without the labour of having to construct the usual SQL insert statement.

The script automatically constructs the full syntax for a SQL insert statement for the table name provided by the user, by repeated reference to the Oracle data dictinary. The user is prompted for a) the table to populate, and then b) an insert value for each attribute in that table.

NOTE that there is no error checking! We assume you enter the correct data when prompted. Any errors will be reported after all attribute values collected and the row insert commences. The script makes considerable use of nested SQL constructs, as developed and presented on my web site: http://peter-robson.port5.com.

--
-- INSERT.SQL
--
-- Author: Peter Robson, December 2001
--
-- Environment: SQL*Plus, any version of Oracle.
--
-- A simple suite of (hidden) SQL scripts which enables the user
-- to insert the odd few rows into any table in the users schema
-- without the labour of having to construct the usual SQL insert
-- statement.
--
-- The script automatically constructs the full syntax for a
-- SQL insert statement for the table name provided by the user,
-- by repeated reference to the Oracle data dictinary.
--
-- The user is prompted for a) the table to populate, and 
-- then b) an insert value for each attribute in that table.
--
-- NOTE that there is no error checking! We assume you enter the
-- correct data when prompted. Any errors will be reported after all
-- attribute values collected and the row insert commences.
-- 
-- Makes considerable use of nested SQL constructs, as developed
-- and presented on my web site: http://peter-robson.port5.com
--------------------------------------------------------------------
--
set echo off
set sqlprompt ""
set trims on
set pagesize 0
set feedback off
set verify off
undefine table
--
-- Important to know current date format in case any attributes
-- to be populated are of type 'date'.
select
 ' '                                        || chr(10) || 
 ' Your default date format is - '||sysdate || chr(10) ||
 ' Following each insert, you may ''commit;'', or ''/'' to add another row...'
 from dual;
--
prompt Enter name of table to populate: 
accept table prompt ' Table: '
--
-- Uses 6 scripts to build up a single insert script
-- into the script file 'ins.sql'
-- 
set termout off
spool ins.sql
------ script 1.
select
'insert into &&table',
' (' from dual;
------ script 2.
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;
------ script 3.
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') ;
------ script 4.
select ') values (' from dual;
------ script 5.
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;
------ script 6.
select
'''&'||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
set feedback on
set trims on
set termout on
--
-- The interactive insert now begins:
--
@ins.sql
--
set feedback on
set sqlprompt "SQL>"
set pagesize 24
set feedback on

For More Information

• What do you think about this tip? E-mail the Edtior at [email protected] with your feedback.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.