Inserting new row in the middle and not at the end
-
HI,
try to image a table wity only 2 fields: id, fruits.
the table is filled, for istance:
id fruits
1 bananas
2 peras
3 peaches
..........................
n fruit
n means that n - row (eg row 10)
Now I would like to insert a new row at the end but, for instance,after peras.
How can I get this result, because if I insert a new row it is placed a the end, in this case at row 10.
Another question...How can I generate an self numbering to avoid to digit every time the increased number in ID field?
I mean if kiwi is the 11st row , sql server insert into ID field 11 (in automatic way)
Thank a lot
-
You can generate an autonumbering ID field by:
Create Table Fruits
(
fruitID int IDENTITY(1,1),
fruitname varchar (50)
)
However, the only way you're going to see the 'fruitname' column inserted in an alphabetical
order (which is what I think you mean ???) is to create a clustered index on the field - but that will only order your 'fruitname' column - your 'fruitID' is still going to show the last generated ID - eg: in your table...
id fruits
1 bananas
2 peras
3 peaches
...if you were to insert 'apple', you would see:
id fruits
4 apple
1 bananas
2 peras
3 peaches
But you can achieve the same thing by just placing an index (doesn't have to be clustered) on the 'fruitname' column and specifically ordering by 'fruitname' when you query this table - depends on what you want to do!!!
**ASCII stupid question, get a stupid ANSI !!!** -
This is something that is easier done at the client.
Just do : Select fruit from dbo.Fruits order by Fruit
Then when you read the recordset, just use the bookmark number or increment a local variable (best solution) and you'll be done.
-
Sorry,
reading back I wrote very bad....
I mean...I don't want to keep fruit col in alphabetic order, I insert data:
id fruits
1 bananas
2 peras
3 peaches
..........................
n fruit
Then I would like to insert a new row in the middle for istance after peras.
But inserting a new row, (eg apples), the new data is inserted at the end, but I want to keep my personal order...
I would like to reach this schema:
id fruits
1 bananas
2 peras
3 apple
4 peaches
..........................
n fruit
The meaning to keep the tables in a specific order is only due to reflex the relation between table (Pk and Fk).
This sould be done in the database.
I used fruit name but they are tables name to be read by a cursor.
Any suggestion?
-
I'm not sure I understand what relationship you want to reflect, but if you make the fruit column a clustered index (see above), you will have apple stored before bananas.....
**ASCII stupid question, get a stupid ANSI !!!** -
Create a clustered index on fruit name column not store the record on the order of forename. If we issue a normally query ‘select * from fruits’ it display the records on the order it is physically store on the table.
But I feel Remi suggestion is more relevant
-
OK.....may be
but if I have a table with all data inserted and then add a new value and I want to have the new data (eg apple) after peras and before peached...how can I do?
Thank again
-
All the things all the other guys are saying are right. I don't know what are you trying to do but if you use Enterprise Manager and choose design table, you can select one of the rows and then right click and select insert row.
I hope that's what you are looking for.
-
That's for inserting a new column in the design. If you try the same thing for an actual row of data, the new line will still be inserted at the end of the table... as it should be.
-
What is the "rule" behind your personal order - you should use this in your "order by" etc..
**ASCII stupid question, get a stupid ANSI !!!** -
Don't discount Joe's comment so easyly, he's 1000000000 % right about this. This is presentation work, and such work should be handled at the client side of the application. If you're still conviced we're wrong then can you tell us why you want to do this (what problem are you trying to solve)?
-
oops! I didn't notice the table only has two colums....duuh!
-
You could add another integer column called displayOrder and populate it so that the rows are displayed in your "custom" order. Of course, any applications would need to be modified to use the new column to sort the data.
id fruits displayOrder
=== ======== ============
1 bananas 1
3 peras 2
4 apple 3
2 peaches 4
-
Joe kelco, ur procedure doesn't seem to work when tried on query analyser
-
CREATE TABLE Foobar
(position INTEGER NOT NULL PRIMARY KEY
CHECK (position > 0),
foo_value CHAR(10) NOT NULL);
GO
INSERT INTO Foobar VALUES (1, 'abc')
INSERT INTO Foobar VALUES (2, 'def')
INSERT INTO Foobar VALUES (4, 'fgh')
GO
CREATE PROCEDURE SwapFoobars (@old_position INTEGER, @new_position INTEGER)
AS
UPDATE Foobar
SET position
= CASE position
WHEN @old_position
THEN @new_position
ELSE position + SIGN(@old_position - @new_position)
END
WHERE position BETWEEN @old_position AND @new_position
OR position BETWEEN @new_position AND @old_position;
GO
CREATE PROCEDURE CloseFoobarGaps
AS
UPDATE Foobar
SET position
= (SELECT COUNT (F1.position)
FROM Foobar AS F1
WHERE F1.position <= Foobar.position);
GO
SELECT * FROM Foobar
EXEC dbo.SwapFoobars 1,2
SELECT * FROM Foobar
EXEC dbo.CloseFoobarGaps
SELECT * FROM Foobar
DROP TABLE Foobar
DROP PROCEDURE SwapFoobars, CloseFoobarGaps
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply