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.
In SQL Server, the basic approach would be:
DECLARE @value VARCHAR(32), @ID INT = 2; UPDATE dbo.table SET ID = ID + 1 WHERE ID >= 2; INSERT dbo.table (ID, Value) SELECT @ID, @Value;
But keep in mind that if these values are referenced in other tables, or end users know what ID = 3 currently refers to, this is going to mess all that up (or not be possible).
Also an important thing to remember is that, by definition, a table is an unordered set of rows - there is no "middle" of a table.