请考虑下表
CREATE TABLE [dbo].[Numbers]
(
[Id] [INT] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
我如何填充它?
如果我添加一列(text nchar(20)
,比方说),那么我可以用以下内容填充它:
insert into numbers (text) values ('')
但是,如果除了标识之外没有其他列,我该怎么办?
insert into numbers ( ) values ( )
这将抛出一个错误
')‘附近的语法不正确。
我想用它来找出缺少序列号的问题。
发布于 2018-10-08 00:47:01
指定DEFAULT VALUES
INSERT INTO dbo.Numbers DEFAULT VALUES;
发布于 2018-10-08 00:50:18
或者,您甚至可以使用SET IDENTITY_INSERT
SET IDENTITY_INSERT YourTable ON;
GO
INSERT INTO YourTable (IdentityColumn) VALUES
(1),
(2),
(3);
GO
SET IDENTITY_INSERT YourTable OFF;
示例:
CREATE TABLE [dbo].[Numbers](
[Id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY];
DECLARE @Start INT=1;
DECLARE @End INT=100;
SET IDENTITY_INSERT [dbo].[Numbers] ON;
WITH Gen AS (
SELECT @Start AS Num
UNION ALL
SELECT Num + 1 FROM Gen WHERE Num + 1 <= @End
)
INSERT INTO [dbo].[Numbers] (Id)
SELECT Num
FROM Gen
OPTION (maxrecursion 100);
SET IDENTITY_INSERT [dbo].[Numbers] OFF;
SELECT *
FROM [dbo].[Numbers];
https://stackoverflow.com/questions/52690651
复制相似问题