Re: creating several rows with one insert?
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 19 Oct 2008 10:27:36 +0200
Message-ID: <48FAEF78.6080909@gmail.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
Date: Sun, 19 Oct 2008 10:27:36 +0200
Message-ID: <48FAEF78.6080909@gmail.com>
Shakespeare schrieb:
> <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 > >
You beat me!
No need in insert all - i need definitely more coffee ;-)
Best regards
Maxim Received on Sun Oct 19 2008 - 03:27:36 CDT