使用增量主键sql插入多行

问题描述：

INSERT INTO TABLE1 (COLUMN1, PRIMARY_KEY)
   SELECT 
      COLUMN1,
      (SELECT COALESCE(MAX(PRIMARY_KEY), 0) 
        FROM TABLE1) + 1 
   FROM 
      TABLE2

错误:

违反主键约束.无法在对象中插入重复的密钥.

如何使第一行之后的主键递增?

How do I make the primary key increment after the first row?

我希望能够同时向此表添加项目列表，而不是将其插入RBAR.

I would like to be able to add a list of items to this table at the same time instead of inserting them RBAR.

感谢您的帮助

答

INSERT INTO TABLE1 (COLUMN1, PRIMARY_KEY)
SELECT COLUMN1,
       (SELECT COALESCE(MAX(PRIMARY_KEY),0)
       FROM TABLE1) + row_number() over (order by 1/0)
FROM TABLE 2

仅对于此语句，ID将是连续的，例如如果Max(Primary Key)为99并且正在插入4条记录，则它们将为100、101、102、103.如果同时插入多个进程，则很容易发生约束冲突，但这并不意味着它比无论如何，使用MAX()来获得单个记录所具有的功能，这本来就是不安全的.

For this statement alone, the IDs will be sequential, e.g. if Max(Primary Key) is 99 and it is inserting 4 records, they will be 100, 101, 102, 103. It's very prone to constraint violations if multiple processes are inserting at the same time, but that's not to say it is any worse than what you have with a single record anyway using MAX() which is inherently unsafe.

使用增量主键sql插入多行

相关推荐