 inserting data between records [message #262659] |
Tue, 28 August 2007 00:51  |
scorpion_4000
Messages: 4
Registered: November 2005
|
Junior Member |
|
|
hi guyz....its my first time here....and a problem brought me here....
the problem is that i m inserting records based on a query that returns 4 rows, and inserts simultaneously....
but i want to insert some data in between them so the sequence doesn't change....as it is a requirement
for example
the data inserted is
:61:0707250725C548667NC010242610
:61:0707250725C9379575NC010564594
:61:0707250725C23060NC010094768
:61:0707250725C40000NC0119072007
but i have to insert ":86:999 Chq Collection" in between them
so the data should look like
:61:0707250725C548667NC010242610
:86:999 Chq Collection
:61:0707250725C9379575NC010564594
:86:999 Chq Collection
:61:0707250725C23060NC010094768
:86:999 Chq Collection
:61:0707250725C40000NC0119072007
:86:999 Chq Collection
so how to do it?????????
Hoping to here from u soon.....
Thanx in advance.......
Regards
Zeeshan
Oracle Developer
|
|
|
|
|
|
| Re: inserting data between records [message #262704 is a reply to message #262668] |
Tue, 28 August 2007 01:50  |
scorpion_4000
Messages: 4
Registered: November 2005
|
Junior Member |
|
|
even if i used a column for recording the order....how will i accomplish this task??????
i was thinking to use a dummy table and insert in master table using cursor....which places one record at a time and inserts data manually between them............will it work?????
thanx for ur time.....
|
|
|
|
|
|
| Re: inserting data between records [message #262775 is a reply to message #262704] |
Tue, 28 August 2007 04:57 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Suppose you have a bag and you start throwing numbered balls into it.
You forget number 5 and continue with numbers 6 and 7 before finding out. What do you do? Empty the bag in order to do it all over?
You should see a table as such a bag. The contents have NO order by themselves.
Only when you retrieve the balls, you define in which order you want them retrieved: number, color, size, whatever. This is the order column Michel mentioned.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: inserting data between records [message #262834 is a reply to message #262829] |
Tue, 28 August 2007 06:17 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Without a column to order by, what you ask for is impossible.
With a column to order by, just pick the values in that column when you insert records in order to give you the order you want.
|
|
|
|
|
|
|
|
|
|
| Re: inserting data between records [message #262935 is a reply to message #262659] |
Tue, 28 August 2007 09:52 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| Quote: | but i want to insert some data in between them so the sequence doesn't change....as it is a requirement
The requirement must be fulfilled but dont know how to do it....
|
If you require to INSERT given rows into positions of table, not defined by any column, then it cannot be fulfiled (as answered before).
Maybe you would suffice with the adjusted SELECT statement resultset.
However, I do not see any order in your data, and if there is no order column in the table, then you may expect output as :61:0707250725C40000NC0119072007
:86:999 Chq Collection
:61:0707250725C23060NC010094768
:86:999 Chq Collection
:61:0707250725C9379575NC010564594
:86:999 Chq Collection
:61:0707250725C548667NC010242610
:86:999 Chq Collection |
|
|
|
|
|
| Re: inserting data between records [message #263613 is a reply to message #262659] |
Thu, 30 August 2007 09:08 |
lenin_babu55
Messages: 12
Registered: August 2007
|
Junior Member |
|
|
Hi,
> Inserting new rows 'between' existing ones does not have any meaning in a relational
> database.
As Guido pointed, there is no point in inserting new rows between existing ones. If you are looking for a solution thats prints rows in the order you defined then have a look at this one:
SQL> create table test( a varchar2(10));Table created.SQL> insert into test select rownum from all_objects where rownum < 5;4 rows created.SQL> insert into test select chr(64+rownum) from all_objects where rownum < 6;5 rows created.SQL> select * from test;A----------1234ABCDE9 rows selected.SQL> commit;Commit complete.SQL> select a from ( 2 select rownum rn, a from test where a = 3 translate(a, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') 5 union all 6 select rownum rn, a from test where a = 7 translate(a, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', 8 '0123456789')) 9 order by rn;A----------A1B2C3D4E9 rows selected.SQL>
Regards
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
