SQL 使用PK将特定位置的一行插入到SQL服务器表中

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

提问by user648610

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?

我想在特定位置的 SQL 服务器表中插入一行。例如，我的表有 100 行，我想在位置 9 插入一个新行。但是表的 PK ID 列已经有一个 ID 为 9 的行。如何在这个位置插入一行，以便所有移到下一个位置后的行？

回答by Remus Rusanu

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:

关系表没有“位置”。作为优化，索引将按指定的键对行进行排序，如果您希望在键顺序中以特定排名插入一行，请使用在该排名位置排序的键插入它。在您的情况下，如果 ID 大于 8，则必须使用值更新所有行以将 ID 增加 1，然后插入值为 9 的 ID：

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.

不用说，做这样的事情不可能有任何合理的理由。如果您真的有这样的要求，那么您将使用具有两个（或更多）部分的复合键。这样的键将允许您插入子键，以便按所需顺序排序。但更有可能的是，您的问题可以通过指定正确的 ORDER BY 来解决，而不会弄乱行的物理顺序。

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.

我想将 ID 为 9 的实体的内容更改为某个新值。实体 9 的旧值应移至 ID 为 10 的实体的内容。ID 为 10 的实体的旧内容应移至 ID 为 11 的实体...等等。应将 ID 最高的实体的旧内容作为新实体插入。

回答by bfavaretto

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

不，您无法控制新行的插入位置。实际上，您不需要：使用语句中的ORDER BY子句SELECT以您需要的方式对结果进行排序。

回答by JK.

Usually you do notwant to use primary keys this way. A better approachwould 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.

如果您的列使用 auto_increment 功能，这将不起作用。

回答by Srinvias Pothu

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

回答by Srinvias Pothu

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 Rowto make a column for the row numbers in your table.

1) 用于ROW_NUMBER() OVER(ORDER BY NameOfColumnToSort ASC) AS Row为表格中的行号制作一列。

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.

2) 从这里，您可以分别SELECT columnsYouNeed INTO使用WHERE Row < ##andRow >= ##语句将表的前后部分复制（使用）到两个单独的表中（基于要在其后插入值的行号）。

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

3）接下来使用DROP TABLE.

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

4) 然后，您将 aUNION用于前表、要插入的行（使用SELECT没有任何其他内容的单个显式定义语句）和后表。到现在为止，您有UNION3 个单独的 select 子句的两个语句。在这里，您可以将其包装在一个SELECT INTO FROM子句中，将其称为原始表的名称。

5) Last, you DROP TABLEthe two tables you made.

5）最后，你制作DROP TABLE的两张桌子。

This is similar to how an ALTER TABLEworks.

这类似于 a 的ALTER TABLE工作方式。

回答by Er. Binod Mehta

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

更多参考：https: //www.techonthenet.com/sql/insert.php