Received: from minbar.arcamax.com (host-10.arcamax.com [206.246.240.138]) by hub.org (8.8.8/8.7.5) with ESMTP id PAA22639 for ; Thu, 27 Aug 1998 15:56:29 -0400 (EDT) Received: from bryan.arcamax.com (bryan [206.246.240.163]) by minbar.arcamax.com (8.8.7/8.8.7) with SMTP id PAA26739; Thu, 27 Aug 1998 15:56:23 -0400 Message-ID: <001601bdd1f4$c3f9df00$a3f0f6ce@bryan.arcamax.com> From: "Bryan White" To: "Walt Bigelow" , Subject: Re: [SQL] copy one to many? Date: Thu, 27 Aug 1998 15:56:22 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 4.72.3110.5 X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 >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.