Insert a row at a specific position in a SQL server table using 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 identifier column, which is PK for the table, already has a row with identifier 9. How can I insert a row at this position so that all rows after going to next position?

Answer 1

Relational tables do not have a "position". As an optimization, the index will sort the rows by the given key, if you want to insert a row in a certain rank in the order of the key, insert it with the key that sorts the positions in that rank. In your case, you will have to update all rows with a value if the ID is greater than 8 to increase the ID from 1, and then insert the ID with a value of 9:

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

Needless to say, there can be no reasonable reason to do something like this. If you really have such a requirement, you should use a composite key with two (or more) parts. Such a key will allow you to insert subsections so that they are sorted in the desired order. But much more likely, your problem can be solved only by indicating the correct ORDER BY, w / o mess with the physical order of the lines.

Another way to look at it is to revise what the primary key means: the identifier of the subject, which does not change during this period of the object. Then your question can be rephrased in such a way as to make the error in your question more obvious:

I want to change the contents of object ID 9 to a few new values. The old values ​​of object 9 must be transferred to the contents of the object with identifier 10. The old contents of the object with identifier 10 must be moved to the object with identifier 11 ... and so on and so forth. The old contents of the object with the highest identifier should be inserted as the new object.

Answer 2

No, you cannot control where the new line is inserted. You don’t really need to: use the ORDER BY in SELECT to arrange the results as you need.

Answer 3

Usually you do not want to use primary keys this way. A better approach would be to create another column called a “line item” or similar, where you can track your own ordering system.

To perform the switch, you can run the following query:

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

This does not work if your column uses auto_increment functions.

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 

Answer 5

This type violates the purpose of the relational table, but if you need to, it is not so difficult to do.

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

2) Here you can copy (using SELECT columnsYouNeed INTO ) before and after parts of a table into two separate tables (based on the row number you want to insert after it) using WHERE Row < ## and Row >= ## respectively.

3) Then you delete the original table using DROP TABLE .

4) Then you use UNION for the before table, the row you want to insert (using one explicitly defined SELECT without any others) and the after table. Currently, you have two UNION statements for 3 separate select statements. Here you can simply wrap this in a SELECT INTO FROM , calling it the name of your source table.

5) Finally, you DROP TABLE completed two tables.

This is similar to how ALTER TABLE works.

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