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

Question

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?

Why do you want to renumber all of the existing values? Hopefully these aren't known, identifiable, or used elsewhere. Also, are you using MySQL or SQL Server? Please pick one. — Aaron Bertrand, Dec 09 '13 at 16:29
There is no such thing as "the middle of the table". Rows in a relational database **ARE NOT SORTED**. — , Dec 09 '13 at 16:31
There is no inherent order inside a table. There is no pre-stablished order when you insert rows. Check [my answer](http://stackoverflow.com/a/16384021/1297603) to a similar question — Yaroslav, Dec 09 '13 at 16:32

Fabian Bigler · Accepted Answer · 2013-12-09T16:43:21.180

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);

score 2 · Answer 2 · answered Dec 09 '13 at 16:32

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.

score 0 · Answer 3 · answered Dec 09 '13 at 16:31

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.

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

3 Answers3

Linked