Insert into a row at specific position into SQL server table with PK

Question

I want to insert a row into a SQL server table at a specific position. For example my table has 100 rows and I want to insert a new row at position 9. But the ID column which is PK for the table already has a row with ID 9. How can I insert a row at this position so that all the rows after it shift to next position?

Spellman

Answer 1

Relational tables have no 'position'. As an optimization, an index will sort rows by the specified key, if you wish to insert a row at a specific rank in the key order, insert it with a key that sorts in that rank position. In your case you'll have to update all rows with a value if ID greater than 8 to increment ID with 1, then insert the ID with value 9:

UPDATE TABLE table SET ID += 1 WHERE ID >= 9;
INSERT INTO TABLE (ID, ...) VALUES (9, ...);

Needless to say, there cannot possibly be any sane reason for doing something like that. If you would truly have such a requirement, then you would use a composite key with two (or more) parts. Such a key would allow you to insert subkeys so that it sorts in the desired order. But much more likely your problem can be solved exclusively by specifying a correct ORDER BY, w/o messing with the physical order of the rows.

Another way to look at it is to reconsider what primary key means: the identifier of an entity, which does not change during that entity lifetime. Then your question can be rephrased in a way that makes the fallacy in your question more obvious:

I want to change the content of the entity with ID 9 to some new value. The old values of the entity 9 should be moved to the content of entity with ID 10. The old content of entity with ID 10 should be moved to the entity with ID 11... and so on and so forth. The old content of the entity with the highest ID should be inserted as a new entity.

Crush

Answer 2

Usually you do not want to use primary keys this way. A better approach would be to create another column called 'position' or similar where you can keep track of your own ordering system.

To perform the shifting you could run a query like this:

UPDATE table SET id = id + 1 WHERE id >= 9

This do not work if your column uses auto_increment functionality.

Slype

Answer 3

No, you can't control where the new row is inserted. Actually, you don't need to: use the ORDER BY clause on your SELECT statements to order the results the way you need.

Montagu

Answer 4

DECLARE  @duplicateTable4 TABLE (id int,data VARCHAR(20))
INSERT INTO @duplicateTable4 VALUES (1,'not duplicate row')
INSERT INTO @duplicateTable4 VALUES (2,'duplicate row')
INSERT INTO @duplicateTable4 VALUES (3,'duplicate rows')
INSERT INTO @duplicateTable4 VALUES (4,'second duplicate row')
INSERT INTO @duplicateTable4 VALUES (5,'second duplicat rows')
DECLARE  @duplicateTable5 TABLE (id int,data VARCHAR(20))

insert into @duplicateTable5 select *from @duplicateTable4
delete from @duplicateTable4


declare @i int , @cnt int
set @i=1
set @cnt=(select count(*) from @duplicateTable5)
while(@i<=@cnt)
begin
if @i=1 
begin
insert into @duplicateTable4(id,data) select 11,'indian' 
insert into @duplicateTable4(id,data) select id,data from @duplicateTable5 where id=@i
end
else
insert into @duplicateTable4(id,data) select id,data from @duplicateTable5 where id=@i
set @i=@i+1
end


    select *from @duplicateTable4

Pontine

this code can be for inserting values at particular position, in this i set position=1 where the new row value inserted at row position 1 – Pontine

Answer 5

This kind of violates the purpose of a relational table, but if you need, it's not really that hard to do.

1) use ROW_NUMBER() OVER(ORDER BY NameOfColumnToSort ASC) AS Row to make a column for the row numbers in your table.

2) From here you can copy (using SELECT columnsYouNeed INTO ) the before and after portions of the table into two separate tables (based on which row number you want to insert your values after) using a WHERE Row < ## and Row >= ## statement respectively.

3) Next you drop the original table using DROP TABLE.

4) Then you use a UNION for the before table, the row you want to insert (using a single explicitly defined SELECT statement without anything else), and the after table. By now you have two UNION statements for 3 separate select clauses. Here you can just wrap this in a SELECT INTO FROM clause calling it the name of your original table.

5) Last, you DROP TABLE the two tables you made.

This is similar to how an ALTER TABLE works.

Aguascalientes

Answer 6

INSERT INTO customers
(customer_id, last_name, first_name)
SELECT employee_number AS customer_id, last_name, first_name
FROM employees
WHERE employee_number < 1003;

FOR MORE REF: https://www.techonthenet.com/sql/insert.php

Burlington