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?
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
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.