4

I have a SQL Server 2016 table with an order/position column. This column is an INT that indicates the preferred order/position of the rows in table for presentation purposes.

How do I INSERT a row in position 1 without using a cursor? (i.e. new row has position 1, existing rows - assumed to be in correct order before insert - must be "moved down")

The order/position column is NOT NULL and enforced to be UNIQUE. I would also prefer not to use "blunt force" technique like set all rows' order/position value to some "never used" values and then just perform reorder on all rows.

MDCCL
  • 8,520
  • 3
  • 30
  • 61
PBMe_HikeIt
  • 141
  • 3

4 Answers4

5

I suppose you want something like this:

BEGIN TRANSACTION ;

    DECLARE @pos INT = 1 ;

    UPDATE tbl
    SET position = position + 1
    WHERE position >= @pos
      AND EXISTS
          (SELECT * FROM tbl WHERE position = @pos) ;

    INSERT INTO tbl
      (<column names>, position)
    VALUES
      (<values ...>, @pos) ;

COMMIT TRANSACTION ;

Note that this design will behave highly inefficiently, if you have often inserts. It will essentially update almost the entire table for every new insert!

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
  • thanks for response, but since my position column is unique (see question) that this would not work after 2 rows in table (unique idx violation)? – PBMe_HikeIt Jun 15 '18 at 15:07
  • I believe that your response combined with this SO will give me what I'm after. Let me check it out – PBMe_HikeIt Jun 15 '18 at 15:17
  • 2
    I don't see how my code could produce a UNIQUE constraint violation. You don't need any ORDER BY for the UPDATE to work. The question you link is different, they want to apply row_numbers. – ypercubeᵀᴹ Jun 15 '18 at 15:45
3

Another common design here it to make the ordering column a float. Then you can always insert new rows before, after, or between the existing rows.

David Browne - Microsoft
  • 46,306
  • 3
  • 46
  • 96
2

If inserting into the data in a certain order is a very common use case maybe it would make sense to model this table like a linked list. Each row would have ID, parent, child. Parent and child columns would store the ID of the row before and after them respectively. The list's root would be "where parent is null".

Now obviously doing this will come at the expense of searching performance. For example, if you want to see if ItemA's position is before ItemB's, you'll need to iterate sequentially through the table until you find one item. O(N) performance I believe.

Edit: Response to: "this table is already part of a larger system so I cannot practically restructure table to make it a linked list" – PBMe_HikeIt

You could add the parent and child columns, then insert values into them based on the current "order" column. Then after every new insert you could iterate through the list and reset the order column to the item's position in the list, reducing both insert and search to O(N).

This might make sense to do as a temporary fix though while phasing out the "order" column.

Brent Sandstrom
  • 145
  • 8
1

The answer is just to move the rest of the rows first, then insert the row:

UPDATE table SET position = position + 1
INSERT INTO table VALUES(1, ...)

Or, if you don't actually know if there is a "row 1" or not:

DECLARE @diff INT

SET @diff = 2 - (SELECT MIN(position) FROM table)
IF @diff > 0 UPDATE table SET position = position + @diff
INSERT INTO table VALUES(1, ...)

The following will generate a position that's one less than the current minimum, but may generate a negative or zero position, so make sure your software can handle that:

INSERT INTO table VALUES((SELECT MIN(position) FROM table) - 1, ...)

Note that you would almost certainly want to wrap any of the above constructs in a transaction.

HiddenWindshield
  • 123
  • 5