SQL Server/Oracle:为非标识列插入select语句中的值(插入…values((select语句)，va

Question

我想从下面这样的select语句中插入(user_id(值。它起作用，但user_id不会增加，因为user_id不是标识列。我想在每次运行插入查询时增加user_id计数。现在还没有发生。我创造了一个数据库小提琴。我希望这段代码同时适用于SQL Server和Oracle版本。

https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=ba954e4d37831a46ea3b3f3b499cc51

CREATE TABLE b_user (
user_id      INT,
user_name    VARCHAR(250),
user_email   VARCHAR(250),
user_address VARCHAR(250),
user_city    VARCHAR(50),
user_state   VARCHAR(5),
user_country VARCHAR(5),
user_zip     VARCHAR(10)
)
insert into b_user (
user_id,
user_name,
user_email,
user_address,
user_city,
user_state,
user_country,
user_zip
) values (
1,
'Alice',
'[e-mail]',
'A house',
'A city',
'STATE',
'ABC',
'ZZ0123'
);
insert into b_user (user_id,
user_name,
user_email,
user_address,
user_city,
user_state,
user_country,
user_zip)
values (
(select max(user_id)  from b_user),
'david brown',
'[e-mail]',
'david''s house',
'chicago',
'il',
'usa',
60007
)

db fiddle 截图

Answer 1

将列定义为identity列：

CREATE TABLE b_user (
user_id      INT IDENTITY(1, 1),
user_name    VARCHAR(250),
user_email   VARCHAR(250),
user_address VARCHAR(250),
user_city    VARCHAR(50),
user_state   VARCHAR(5),
user_country VARCHAR(5),
user_zip     VARCHAR(10)
);

然后将其从INSERT中移除。它会自动分配一个值。

编辑：

如果您无法修改表格，您可以尝试：

values (
(select coalesce(max(user_id) + 1, 1)  from b_user),
'david brown',
'[e-mail]',
'david''s house',
'chicago',
'il',
'usa',
60007
)

这不是线程安全的，不推荐使用。这就是为什么identity列很有用。

Answer 2

您可以创建一个从您的最大ID开始的序列。然后您可以为序列选择下一个值，以便轻松递增。

EDIT：如果您需要从表中的值开始的序列，您可以包装在这里描述的exec语句中

CREATE SEQUENCE IDSeq  
START WITH 2  
INCREMENT BY 1 ;  
GO

然后

values (
(NEXT VALUE FOR IDSeq),
'david brown',
'[e-mail]',
'david''s house',
'chicago',
'il',
'usa',
60007
)