Insert data into a table while preserving the order
Hi All,
I have a table with ID values from A thro' S with some values
in between A and S missing. Now, after the table has been in
production for a while, I need to insert more rows. However,
this is disturbing the alphabetic order I have originally on
the ID field. Just looking for ways to see if I can insert the
values at the appropriate locations.
Here are the SQL statements that I used.
CREATE TABLE PARTS (ID VARCHAR2(10), DESCRIPTION VARCHAR2(25));
INSERT INTO PARTS VALUES ('A', 'HARDWARE');
INSERT INTO PARTS VALUES ('B', 'SOFTWARE');
INSERT INTO PARTS VALUES ('D', 'HARDWARE');
INSERT INTO PARTS VALUES ('J', 'SOFTWARE');
INSERT INTO PARTS VALUES ('M', 'HARDWARE');
INSERT INTO PARTS VALUES ('P', 'HARDWARE');
INSERT INTO PARTS VALUES ('Q', 'HARDWARE');
INSERT INTO PARTS VALUES ('S', 'SERVICES');
SELECT * FROM PARTS;
ID DESCRIPTION
== ===========
A HARDWARE
B SOFTWARE
D HARDWARE
J SOFTWARE
M HARDWARE
P HARDWARE
Q HARDWARE
S SERVICES
Now, I need to enter the following values into the table.
INSERT INTO PARTS VALUES ('AL', 'ADJUSTMENT');
INSERT INTO PARTS VALUES ('xx', 'UNKNOWN');
INSERT INTO PARTS VALUES ('H', 'HARDWARE');
INSERT INTO PARTS VALUES ('N', 'SUBSCRIPTION');
INSERT INTO PARTS VALUES ('O', 'OTHER');
If I do a select * from this table, the id values are not sorted alpahbetically any more. I cannot afford to drop the table
and create again. Is there anyway I can enter these values in the
alphabetic sequence so the ID values will show up as A, B, D, H,
J, M, N, O, P, Q, S, Al, xx?
I can get AL and xx as the last two values if I have those 2 inserts
at the end in that order. My question is about inserting the values
H, N and O in the appropriate places.
Oracle version is 9.2.0.2.0 and I am running on Solaris 9.
Any help would be greatly appreciated.
Many Thanks.
Sankar
Re: Insert data into a table while preserving the order
Quote:
Originally posted by sankar6254
Hi All,
I have a table with ID values from A thro' S with some values
in between A and S missing. Now, after the table has been in
production for a while, I need to insert more rows. However,
this is disturbing the alphabetic order I have originally on
the ID field. Just looking for ways to see if I can insert the
values at the appropriate locations.
Here are the SQL statements that I used.
CREATE TABLE PARTS (ID VARCHAR2(10), DESCRIPTION VARCHAR2(25));
INSERT INTO PARTS VALUES ('A', 'HARDWARE');
INSERT INTO PARTS VALUES ('B', 'SOFTWARE');
INSERT INTO PARTS VALUES ('D', 'HARDWARE');
INSERT INTO PARTS VALUES ('J', 'SOFTWARE');
INSERT INTO PARTS VALUES ('M', 'HARDWARE');
INSERT INTO PARTS VALUES ('P', 'HARDWARE');
INSERT INTO PARTS VALUES ('Q', 'HARDWARE');
INSERT INTO PARTS VALUES ('S', 'SERVICES');
SELECT * FROM PARTS;
ID DESCRIPTION
== ===========
A HARDWARE
B SOFTWARE
D HARDWARE
J SOFTWARE
M HARDWARE
P HARDWARE
Q HARDWARE
S SERVICES
Now, I need to enter the following values into the table.
INSERT INTO PARTS VALUES ('AL', 'ADJUSTMENT');
INSERT INTO PARTS VALUES ('xx', 'UNKNOWN');
INSERT INTO PARTS VALUES ('H', 'HARDWARE');
INSERT INTO PARTS VALUES ('N', 'SUBSCRIPTION');
INSERT INTO PARTS VALUES ('O', 'OTHER');
If I do a select * from this table, the id values are not sorted alpahbetically any more. I cannot afford to drop the table
and create again. Is there anyway I can enter these values in the
alphabetic sequence so the ID values will show up as A, B, D, H,
J, M, N, O, P, Q, S, Al, xx?
I can get AL and xx as the last two values if I have those 2 inserts
at the end in that order. My question is about inserting the values
H, N and O in the appropriate places.
Oracle version is 9.2.0.2.0 and I am running on Solaris 9.
Any help would be greatly appreciated.
Many Thanks.
Sankar
why do you want to do this at all ?