selecting and inserting into the same table 1

[lel75]

Hello,

I have the following select insert into statement:

insert into student
select student_id, student_cd, student_name
from student
where student_id = '9999'

The two columns - student_id and student_cd are primary keys.

When I try to insert, it gives me a 'unique constraint violated' error. Can you please tell me how I can use the insert above and increment the student_cd value by 1 of the max(student_cd) each time to avoid the constraint issue? or to just add 1000 each time to the student_cd value?

Thanks in advance.

Cathy

 

[SantaMufasa]

Lel,

Why would you want to duplicate some other student's student_name and student_cd? In any case, here is the code you want:

insert into student
select student_id+1, student_cd, student_name
   from student, (select max(student_id) last_id from student)
   where student_id = last_id;

Let me know if this is what you wanted,

Dave
Sandy, Utah, USA @ 17:39 (19Nov03) GMT, 10:39 (19Nov03) Mountain Time

 

[lel75]

Dave,

I guess I should of clearly explained what I wanted to do. Currently there are over 1000 records in this table (stupid of me to use student table as an example) that I'm trying to duplicate values for. I would like to duplicate those 1000+ records so that I have over 2000 records in that table because I need to perform some tests. Basically I would just like to copy all the records in a table and insert it again into that table instead of having to manually enter in all these additional records (which is too time consuming) but the constraint issue is the problem here. I hope that helps.

Cathy

 

[SantaMufasa]

Lel,

The more information you give, the closer I get to providing the solution you want.

In that case, I recommend the following:

1) SELECT MAX(student_id) from student;
2) create sequence student_seq start with <whatever_value_you_get_from_the_above_SELECT>;
3) INSERT INTO student student_seq.nextval, student_cd, student_name from student;

Voila, doubled your student table with unique ids.

Dave
Sandy, Utah, USA @ 18:31 (19Nov03) GMT, 11:31 (19Nov03) Mountain Time

 

[SantaMufasa]

The moment I hit [Submit Post], I realised my goof: Step 2 should read:

2) ) create sequence student_seq start with <whatever_value_you_get_from_the_above_SELECT+1>;

Note the &quot;+1&quot;. Sorry.

Dave
Sandy, Utah, USA @ 18:33 (19Nov03) GMT, 11:33 (19Nov03) Mountain Time

 

[lel75]

Dave,

That worked! Thanks again and again for your help!

Cathy