How to insert N rows of default values into a table

Question

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)
)

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?

Current solution (edited some input since it didn't make any sense)

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

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

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.

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

Answer 1

An easier way is:

insert dbo.OrderStatus default values
go 500

this will insert 500 rows of default values.

Answer 2

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

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

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.