pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcopy one to many?
4+ messages / 3 participants
[nested] [flat]
* copy one to many?
@ 1998-08-27 19:38 Walt Bigelow <walt@stimpy.com>
0 siblings, 0 replies; 4+ messages in thread
From: Walt Bigelow @ 1998-08-27 19:38 UTC (permalink / raw)
To: pgsql-sql
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,
spottitle,
isci,
trt,
starttimecode,
date,
audiotypeid)
SELECT
'6666',
spotnumber,
spottitle,
isci,
trt,
starttimecode,
date,
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'm kinda stuck, since the only docs I have on SQL is a crappy MS Access
'developers' book. :-(
Thanks,
Walt
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] copy one to many?
@ 1998-08-27 19:56 Bryan White <bryan@arcamax.com>
0 siblings, 2 replies; 4+ messages in thread
From: Bryan White @ 1998-08-27 19:56 UTC (permalink / raw)
To: Walt Bigelow <walt@stimpy.com>; pgsql-sql
>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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] copy one to many?
@ 1998-08-28 03:06 Walt Bigelow <walt@stimpy.com>
parent: Bryan White <bryan@arcamax.com>
1 sibling, 0 replies; 4+ messages in thread
From: Walt Bigelow @ 1998-08-28 03:06 UTC (permalink / raw)
To: Bryan White <bryan@arcamax.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] copy one to many?
@ 1998-08-30 08:58 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Bryan White <bryan@arcamax.com>
1 sibling, 0 replies; 4+ messages in thread
From: Herouth Maoz @ 1998-08-30 08:58 UTC (permalink / raw)
To: Walt Bigelow <walt@stimpy.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~1998-08-30 08:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-08-27 19:38 copy one to many? Walt Bigelow <walt@stimpy.com>
1998-08-27 19:56 Re: [SQL] copy one to many? Bryan White <bryan@arcamax.com>
1998-08-28 03:06 ` Walt Bigelow <walt@stimpy.com>
1998-08-30 08:58 ` Herouth Maoz <herouth@oumail.openu.ac.il>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox