Re: creating several rows with one insert?

<mh_at_pixar.com> schreef in bericht
news:ERBKk.3475$D32.153_at_flpi146.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 Received on Sun Oct 19 2008 - 03:21:16 CDT