Re: creating several rows with one insert?
- From: Maxim Demenko <mdemenko@xxxxxxxxx>
- Date: Sun, 19 Oct 2008 10:24:49 +0200
mh@xxxxxxxxx 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
.
- Follow-Ups:
- References:
- Prev by Date: Re: creating several rows with one insert?
- Next by Date: Re: creating several rows with one insert?
- Previous by thread: Re: creating several rows with one insert?
- Next by thread: Re: creating several rows with one insert?
- Index(es):
Relevant Pages
|
Loading
