如何在表中插入 N 行默认值

2022-01-09 00:00:00 sql insert tsql sql-server

我有一个表，其中包含一个标识列以及一个表示创建日期的列:

I have a table containing an identity column as well as a column representing the creation date:

CREATE TABLE dbo.OrderStatus
(
    OrderStatusId int IDENTITY(1, 1) NOT NULL,
    CreationDate datetime NOT NULL default GETDATE(),
    CONSTRAINT PK_OrderStatus PRIMARY KEY(OrderStatusId)
)

由于标识列自己生成一个值，并且 CreationDate 始终是当前日期(GETDATE())，我可以添加一行感谢 DEFAULT VALUES:

Since the identity column generates a value by itself and the CreationDate is always going to be the current date (GETDATE()), I can add a row thanks to DEFAULT VALUES:

INSERT INTO dbo.OrderStatus DEFAULT VALUES;

但是如果我想添加，比如说，三个记录，我该怎么办?

But what can I do if I want to add, let's say, three records?

当前解决方案(编辑了一些输入，因为它没有任何意义)

现在，为了做我想做的事，我用 VALUES 添加了几行:

For now, in order to do what I want, I add several rows with VALUES:

INSERT INTO dbo.OrderStatus (CreationDate)
VALUES  (GETDATE()), 
        (GETDATE()), 
        (GETDATE())

虽然，我更愿意知道多行的 INSERT INTO .. DEFAULT VALUES 的等效项，以防我稍后添加具有默认值的另一列.

Although, I'd prefer to know the equivalent of INSERT INTO .. DEFAULT VALUES for multiple rows, in case that I add another column with a default value later on.

有没有办法用 DEFAULT VALUES 或类似的方式将 N 行插入到表中?

Is there a way to insert N rows into a table with DEFAULT VALUES or in a similar way?

推荐答案

你可以使用你原来的定义，只使用一个while循环，例如

You can use your original definition and just use a while loop, for example

DECLARE  @OrderStatus TABLE
(
    OrderStatusId int IDENTITY(1, 1) NOT NULL,
    CreationDate datetime NOT NULL DEFAULT GETDATE()
    --CONSTRAINT PK_OrderStatus PRIMARY KEY(OrderStatusId) -- this can be uncommented if creating a real table.
)


DECLARE @i int = 0;

WHILE @i < 100 -- insert 100 rows.  change this value to whatever you want.
BEGIN

INSERT @OrderStatus DEFAULT VALUES
SET @i = @i + 1;

END

SELECT * FROM @OrderStatus

以下是使用递归 CTE 的方法:

Here's how to do it using a recursive CTE:

;with cteNums(n) AS
(
    SELECT 1
    UNION ALL
    SELECT n + 1
    FROM cteNums WHERE n < 100 -- how many times to iterate
)
INSERT @OrderStatus 
SELECT * FROM cteNums

请注意，对于 CTE，如果它大于 100，您必须指定 OPTION(MAXRECURSION ...).另外请注意，即使您从CTE，它们实际上并没有被插入到表中.

Just note that for the CTE you'd have to specify OPTION(MAXRECURSION ...) if it's greater than 100. Also note that even though you're selecting a list of numbers from the CTE, they don't actually get inserted into the table.

相关文章