我需要一个自动递增的id,而在下面的代码中,它每隔几秒钟插入一次到表中。
当前id列为null。
有什么办法吗?
INSERT INTO [display].[dbo].[data1] (position, action, sync)
SELECT
CAST(RAND() * 3 AS INT) + 1,
CASE CAST(RAND() * 8 AS INT)
WHEN 0 THEN 'Weighlifting'
WHEN 1 THEN 'Shoutout'
WHEN 2 THEN 'dumbbells'
WHEN 3 THEN 'facewipe'
WHEN 4 THEN 'pacman'
WHEN 5 THEN 'shootingstar'
WHEN 6 THEN 'tornado'
WHEN 7 THEN 'muscle'
END,
CAST(RAND() * 4 AS INT)
WAITFOR DELAY '00:00:01.500'
GO 3000在此之前:
id position action sync
-----------------------------------
NULL 3 facewipe 2
NULL 1 tornado 1
NULL 1 tornado 3
NULL 1 Shoutout 3
NULL 2 NULL 0
NULL 1 dumbbells 2
NULL 2 Shoutout 2 预期表:
id position action sync
-----------------------------------
1 3 facewipe 2
2 1 tornado 1
3 1 tornado 3
4 1 Shoutout 3
5 2 NULL 0
6 1 dumbbells 2
7 2 Shoutout 2 发布于 2020-03-06 07:06:30
将数据类型声明为id列的IDENTITY。
ALTER TABLE dbo.YourTable DROP COLUMN ID;
ALTER TABLE dbo.YourTable ADD ID INT IDENTITY;这将为现有行添加增量no。
更新现有行的替代方法:
DECLARE @id INT
SET @id = 0
UPDATE table SET @id = id = @id + 1 https://stackoverflow.com/questions/60559065
复制相似问题