SQL How to insert a new row in the middle of the table

Here is my problem:

I want to insert a new row in my table but there is already some registers in it. If I need to put this new row at the same row that a already register is, what should I do?

For example:

I have this table with this rows:

ID|Value
1 |Sample1
2 |Sample2
3 |Sample3

But now I want to insert a new row where Sample2 is, so the table should be like:

ID|Value
1 |Sample1
2 |NewSample
3 |Sample2
4 |Sample3

Any thoughts?

Answers


Any thoughts?

Yes. Please forget about changing the primary key (the ID) if you have references somewhere.

Rather add a column (e.g. ViewOrder) which is handling this explicitly for you:

ID|Value     | ViewOrder
1 |Sample1   |1
5 |NewSample |2
2 |Sample2   |3
3 |Sample3   |4

Query to select:

SELECT ID, Value, ViewOrder FROM yourTable ORDER BY ViewORDER

Insert / Update would look something like this (whereas YourRowIndex is the index where you wish to insert your new row, of course):

UPDATE dbo.table SET VIEWORDER = VIEWORDER + 1 WHERE VIEWORDER >= @YourRowIndex ;
SET IDENTITY_INSERT dbo.table ON
INSERT dbo.table (Value, ViewOrder) VALUES (@YourValue, @YourRowIndex);

The easy way is to add a new column -- set it to the same value as ID and then you have two choices, if you make it numeric you can just add a value in between

ID | Value     | OrderCol
1  | Sample1   | 1
4  | NewSample | 1.5
2  | Sample2   | 2
3  | Sample3   | 3

your other option is to renumber order -- which can be slow if you have a lot of stuff in the table.

You probably don't want to change ID since there might be an external table which references this identifier.


Need Your Help

What is the easiest way to design Java Swing GUIs in Eclipse IDE?

java eclipse swing user-interface ide

I've been coding basic UIs in Java manually and using NetBeans, but recently switched to Eclipse Indigo. I use the visual class builder to design SWT user interfaces in Eclipse but found out that i...

Detection of pattern of circles using opencv

image-processing opencv pattern-matching computer-vision object-recognition

I have to detect the pattern of 6 circles using opencv. I have detected the circles and their centroids by using thresholding and contour function in opencv.