STRANGE HAPPNES WHEN INSERTING AFTER DELETE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

STRANGE HAPPNES WHEN INSERTING AFTER DELETE

Author

Topic

good_friend
Starting Member

2 Posts

Posted - 2008-08-07 : 15:38:28

HELLO, ALL

i am new to sql server

so my problem is also little for you but i am confusing please answer

problem is,

i have one table with 11 records in it

nirav 12121
bhaumik 3345
nishkal 7676
anirudhha 697
nishesh 79876
donkey 12345
hkhhl 645
yahoo 46643
live 86855
gongo 3668
google 9880

when i insert or update records there is no problem,

but when i delete lets say row no 6

THE RESULT LOOK LIKE THIS

nirav 12121
bhaumik 3345
nishkal 7676
anirudhha 697
nishesh 79876
hkhhl 645
yahoo 46643
live 86855
gongo 3668
google 9880

and..

then when i insert a new record it is inserted at 4th position and then the record are continued to be added from that position to downwards
and not at the end of table(means, not from last record onwards)

LIKE IF I INSERT WITH STATEMENT

INSERT INTO try1
VALUES ('VICKY', 6451)

RECORD WILL WE LIKE

nirav 12121
bhaumik 3345
nishkal 7676
anirudhha 697
nishesh 79876
VICKY 6451
hkhhl 645
yahoo 46643
live 86855
gongo 3668
google 9880

WHICH IS NOT DESIRED

AND IT MUST BE LIKE

nirav 12121
bhaumik 3345
nishkal 7676
anirudhha 697
nishesh 79876
hkhhl 645
yahoo 46643
live 86855
gongo 3668
google 9880
VICKY 6451
WHY THIS RESULT IS NOT THERE??

what happens and what is the solution???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-07 : 15:40:50

There's no way to guarantee how the data is stored, but you can affect how to display it. So if you want to view the results in a certain order, then use ORDER BY.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-07 : 16:36:54

Also I think you are going to have to implement an identity on this table otherwise there's no logical way of ordering it in the way you want (you don't want alphabetical or ascending / decending numerical order - you seem to want to order by when the value was inserted into the table).

You'd need to do something like ....


CREATE TABLE testTable (
	[Id] INT IDENTITY(1,1)
	, [data] VARCHAR(50)
	, [value] INT
)

INSERT INTO testTable ([data], [value]) SELECT 'nirav', 12121
INSERT INTO testTable ([data], [value]) SELECT 'bhaumik', 3345
INSERT INTO testTable ([data], [value]) SELECT 'nishkal', 7676
INSERT INTO testTable ([data], [value]) SELECT 'anirudhha', 697
INSERT INTO testTable ([data], [value]) SELECT 'nishesh', 79876
INSERT INTO testTable ([data], [value]) SELECT 'donkey', 12345
INSERT INTO testTable ([data], [value]) SELECT 'hkhhl', 645
INSERT INTO testTable ([data], [value]) SELECT 'yahoo', 46643
INSERT INTO testTable ([data], [value]) SELECT 'live', 86855
INSERT INTO testTable ([data], [value]) SELECT 'gongo', 3668
INSERT INTO testTable ([data], [value]) SELECT 'google', 9880

-- show the table
SELECT * FROM testTable ORDER BY [ID] ASC

Note now that an integer value has been inserted for each row in the [Id] column. You can use this ID to uniquely identify the row for deletes etc and whenver you insert a new row it will get a new highest Id value.

Please read up on identity in books on line for a more detailed explanation.

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Hope this helps.

-------------
Charlie

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-07 : 18:36:26

Charlie makes some good points. Without knowing your data it is hard to say for sure what the best solution for your business need might be. However, if those two columns comprise your natrual key, then there is no need to add a surrogate key (although it might be a valid solution). But, adding a DATETIME column, maybe something like InsertDate or CreateDate might be what you are looking for.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-08-07 : 18:56:15

yes, just add a CreateDate col with default value of getdate(). then just order by that column when you do your select.

great username btw

elsasoft.org

good_friend
Starting Member

2 Posts

Posted - 2008-08-08 : 03:44:11

tkizer,lamprey,transact charlie,jezemine

Thank you all

Thank you all for your kind reply
It helps much in my learning phase
-----------------------------------------------
"" GOD IS NOT WITH YOU BUT GOD IS INSIDE YOU "" - 'Pandurang Shastri'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources