 | |
creating several rows with one insert?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
creating several rows with one insert? -
10-19-2008
, 03:03 AM
#2
| |||
| |||
|
|
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 -- Mark Harrison Pixar Animation Studios |
Shakespeare
#3
| |||
| |||
|
|
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 -- Mark Harrison Pixar Animation Studios |
Shakespeare
#4
| |||
| |||
|
|
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 -- Mark Harrison Pixar Animation Studios |
Shakespeare
#5
| |||
| |||
|
|
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 -- Mark Harrison Pixar Animation Studios |
Shakespeare
#6
| |||
| |||
|
|
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 |
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/B...3a.htm#2125349
Best regards
Maxim
#7
| |||
| |||
|
|
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 |
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/B...3a.htm#2125349
Best regards
Maxim
#8
| |||
| |||
|
|
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 |
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/B...3a.htm#2125349
Best regards
Maxim
#9
| |||
| |||
|
|
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 |
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/B...3a.htm#2125349
Best regards
Maxim
#10
| |||
| |||
|
|
mh (AT) pixar (DOT) com> schreef in bericht news:ERBKk.3475$D32.153 (AT) flpi146 (DOT) ffdc.sbc.com... 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 -- Mark Harrison Pixar Animation Studios insert into test2 select t.*,'99' from test t Shakespeare |
No need in insert all - i need definitely more coffee ;-)
Best regards
Maxim
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.