Re: How to duplicate many rows from SQL

From: Erik <ecotsonas_at_saraswati.com>
Date: Tue, 15 Jun 1999 14:19:43 GMT
Message-ID: <7k5ndj$n95$1@nnrp1.deja.com>

In article <37664956.8BCD0443_at_cern.ch>,   "Stephen Jackson,SL/BI" <Stephen.Jackson_at_cern.ch> wrote:
>
> Hi,
>
> Here's my problem. I want to duplicate create a new set of
records,
> based
> on an existing set of records, but changing one field so as not to
> violate a
> constraint.
>
> So far, the only solution I have (which works) is the following:
>
> create table temp_table as select * from emp where salary >9000;
> update temp_table set emp_type = 321;
> insert into emp select * from temp_table;
> drop table temp_table;
>
> This copies the data I want to duplicate into a temp table,
changes
> it
> appropriately, then copies it back to the original table.
>
> Is there a better way of doing this from SQL?
>
> Cheers,
>
> Stephen Jackson
>
> If you reply via eMail, please send to Stephen.Jackson_at_cern.ch
>
>

You can do an insert statement with a sub-select. Ex:
insert into emp
 (select EMPNO,

           ENAME,
           JOB,
           MGR,
           HIREDATE,
           SAL,
           COMM,
           DEPTNO,
           321         <<<<<<< simply set the new values

   from emp
  where sal >= 2500)

The above will insert 5 new 'copied' records, but with the emp_type set to '321'.

Hope this helps,
Erik
--
Consultant
Saraswati Systems Corporation - (SSC)

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Jun 15 1999 - 09:19:43 CDT