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.


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.


Need Your Help

IE warning workaround? Page w/Secure and insecure items

javascript php internet-explorer https

I have a page that is viewed secured with 'https' in the URL, that also contains youtube urls to play video from youtube. Since the youtube URL contains 'http' with no 's' IE is giving an a warning

Are there situations where the observer pattern is better than the publish subscriber pattern?

javascript design-patterns observer-pattern publish-subscribe

I am reading http://addyosmani.com/resources/essentialjsdesignpatterns/book/#designpatternsjavascript to learn more about design patterns.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.