如何在表格中插入N行默认值 [英] How to insert N rows of default values into a table

问题描述

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

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.

这篇关于如何在表格中插入N行默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！