Re: [SQL] copy one to many?

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