Re: creating several rows with one insert?

mh_at_pixar.com schrieb:
> suppose I have a table t1 with one column and several rows of data:
>
> name
> ----
> foo
> bar
> baz
>
> and that I want to insert some rows into another table,
> driven by the data in t1.
>
> So that If I insert the value 99, I would get the equivalent
> of:
>
> insert into t2(a,b) values(99,'foo');
> insert into t2(a,b) values(99,'bar');
> insert into t2(a,b) values(99,'baz');
>
> Now I'm handling this in my client code with the obvious code:
>
> x=99
> curs.execute('select name from t1')
> for n in curs:
> curs.execute('insert into t2(a,b) values(:1,:2)',[x,n])
>
> 1. Is there some clever way to cause this to happen in SQL,
> i.e. a built-in iterator over the rows in t1?
>
> 2. This is how I was planning to implement the code in
> a stored procedure. Is there a better way to do this?
>
> for rec in (select name from t1) loop
> insert into t1(a,b) values (x,rec.name);
> end loop;
>
> Many TIA!
> Mark
>

sqlplus scott/tiger

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Oct 19 10:20:14 2008

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t1(name) as select 'foo' from dual

   2 union all select 'bar' from dual union all select 'baz' from dual;

Table created.

SQL> create table t2(a number,b varchar2(10));

Table created.

SQL> insert all into t2 values(99,name) select * from t1;

3 rows created.

SQL> select * from t2;

          A B
---------- ----------

         99 foo
         99 bar
         99 baz

Syntax examples (from 9iR2 onwards) :
http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_913a.htm#2125349

Best regards

Maxim Received on Sun Oct 19 2008 - 03:24:49 CDT