Home » SQL & PL/SQL » SQL & PL/SQL » inserting data between records
icon9.gif  inserting data between records [message #262659] Tue, 28 August 2007 00:51 Go to next message
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 #262668 is a reply to message #262659] Tue, 28 August 2007 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no order in a (heap) table so insert order does not matter.
More youo can't do what you want. You must have a column to define the order of your rows.

Regards
Michel
Re: inserting data between records [message #262704 is a reply to message #262668] Tue, 28 August 2007 01:50 Go to previous messageGo to next message
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 #262739 is a reply to message #262704] Tue, 28 August 2007 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't insert in order.
You then select in the order using the order column.

Regards
Michel
Re: inserting data between records [message #262775 is a reply to message #262704] Tue, 28 August 2007 04:57 Go to previous messageGo to next message
Frank
Messages: 7880
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 #262783 is a reply to message #262775] Tue, 28 August 2007 05:02 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Nice analogy. I like it Smile
Re: inserting data between records [message #262795 is a reply to message #262783] Tue, 28 August 2007 05:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Thanks, although I must admit I saw it somehwere before in this forum.
Now to find a good way of explaining how the order by is done (peeking before fetching?)
Re: inserting data between records [message #262797 is a reply to message #262795] Tue, 28 August 2007 05:20 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I wait with baited breath Frank Very Happy
Re: inserting data between records [message #262828 is a reply to message #262775] Tue, 28 August 2007 06:07 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Its Too NICE.....

icon5.gif  Re: inserting data between records [message #262829 is a reply to message #262797] Tue, 28 August 2007 06:10 Go to previous messageGo to next message
scorpion_4000
Messages: 4
Registered: November 2005
Junior Member
Nice to see such interesting and fast replies.....

But the problem is.....i m standing where i was before.....
The requirement must be fulfilled but dont know how to do it....
Embarassed
Re: inserting data between records [message #262834 is a reply to message #262829] Tue, 28 August 2007 06:17 Go to previous messageGo to next message
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 #262854 is a reply to message #262834] Tue, 28 August 2007 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Without a column to order by, what you ask for is impossible.

More it is meaningless as Frank's example show it.

Regards
Michel
Re: inserting data between records [message #262860 is a reply to message #262854] Tue, 28 August 2007 06:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Interesting semantic point - is it possible for a request that is impossible to fulfil to be anything other than meaningless?
Re: inserting data between records [message #262869 is a reply to message #262860] Tue, 28 August 2007 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


./fa/917/0/

Re: inserting data between records [message #262935 is a reply to message #262659] Tue, 28 August 2007 09:52 Go to previous messageGo to next message
flyboy
Messages: 1832
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
(by the way, similar output without the new rows may be produced over initially inserted data without ORDER BY clause on order column).
Re: inserting data between records [message #262972 is a reply to message #262795] Tue, 28 August 2007 12:27 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Frank wrote on Tue, 28 August 2007 06:17
Thanks, although I must admit I saw it somehwere before in this forum.


I recall anacedent making a similar analogy. Maybe that's the one.
Re: inserting data between records [message #263613 is a reply to message #262659] Thu, 30 August 2007 09:08 Go to previous messageGo to next message
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
Re: inserting data between records [message #263615 is a reply to message #263613] Thu, 30 August 2007 09:11 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should read OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel

[Updated on: Thu, 30 August 2007 09:12]

Report message to a moderator

Previous Topic: i need help due_date query
Next Topic: procedure for insert and update
Goto Forum:
  


Current Time: Sun Dec 04 04:17:51 CST 2016

Total time taken to generate the page: 0.12894 seconds