Thread: Re: [SQL] copy one to many?
>This is a follow up to my last question on how to use INSERT INTO to copy
>rows. I have one more hurdle... how can I copy one or more source rows to
>many dest rows, but with different target library numbers?
>
>I have this:
>INSERT INTO tblspotinfo
> (librarynumber,
> spotnumber,
> audiotypeid
...
> FROM
> tblspotinfo
> WHERE
> librarynumber = '9988';
>
>Is there a way to say, get all records with the library number = '9988'
>and copy them to a list of NEW library numbers?
>
>So source would be librarynumber 9988,
>and dest would be 4457, 4458, 4459, 4460 instead of 6666.
>
>Is SQL capabile of this or do I need a function on the server side to do
>this easily?
I think you can use a sequence to do this. Look at the man page for
create_sequence. I think next_seq is the built in function to retrieve the
next id. You could call that as a column in the select portion of your
statement: ie:
CREATE SEQUENCE myseq start 4457;
INSERT into tblspotinfo (...) SELECT next_seq('myseq'), ... FROM ... WHERE
...;
DROP SEQUENCE myseq;
Of course you would only do the DROP if you were not going to use the
sequence again.
On Thu, 27 Aug 1998, Bryan White wrote:
> >This is a follow up to my last question on how to use INSERT INTO to copy
> >rows. I have one more hurdle... how can I copy one or more source rows to
> >many dest rows, but with different target library numbers?
> >
> >I have this:
> >INSERT INTO tblspotinfo
> > (librarynumber,
> > spotnumber,
> > audiotypeid
> ...
> > FROM
> > tblspotinfo
> > WHERE
> > librarynumber = '9988';
> >
> >Is there a way to say, get all records with the library number = '9988'
> >and copy them to a list of NEW library numbers?
> >
> >So source would be librarynumber 9988,
> >and dest would be 4457, 4458, 4459, 4460 instead of 6666.
> >
> >Is SQL capabile of this or do I need a function on the server side to do
> >this easily?
>
>
> I think you can use a sequence to do this. Look at the man page for
> create_sequence. I think next_seq is the built in function to retrieve the
> next id. You could call that as a column in the select portion of your
> statement: ie:
> CREATE SEQUENCE myseq start 4457;
> INSERT into tblspotinfo (...) SELECT next_seq('myseq'), ... FROM ... WHERE
> ...;
> DROP SEQUENCE myseq;
>
> Of course you would only do the DROP if you were not going to use the
> sequence again.
>
>
>
I was looking more on the lines of a list of dest values. This list is by
no means in order, or sequential.
For example: I want to copy this data TO this list of lib #'s
3422, 4456, 7743... etc (baiscally random)
I'd prefer to have 1 insert statment do the job. Right now I have M$
Access doing this in a for/next loop, changing the dest lib number for
each in the list.
I'd prefer to do this on the server end. Whould this be easy to do with
server side funcs?
Thanks for all your help,
Walt
At 6:06 +0300 on 28/8/98, Walt Bigelow wrote: > I was looking more on the lines of a list of dest values. This list is by > no means in order, or sequential. > > For example: I want to copy this data TO this list of lib #'s > > 3422, 4456, 7743... etc (baiscally random) > > I'd prefer to have 1 insert statment do the job. Right now I have M$ > Access doing this in a for/next loop, changing the dest lib number for > each in the list. > > I'd prefer to do this on the server end. Whould this be easy to do with > server side funcs? Well.... SQL was NOT designed as a procedural language, and when you have data of a 'random' nature, as you said, you need a procedural languague, which the current backend doesn't have. If you want to insert just the new lib numbers into an auxiliary table, you can easily do the insert as a cartesian product between the rows you want to duplicates and the rows in the auxiliary table. Of course, then you'll need a loop for inserting the lib values into the auxiliary table - or you can do that with a COPY. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma