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

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.

Answer 2

An easier way is:

insert dbo.OrderStatus default values
go 500

this will insert 500 rows of default values.

Answer 3

Set up a trigger when a new row is CREATEd:

https://msdn.microsoft.com/en-us/library/ms189799.aspx

Answer 4

The Tally Table method can insert large sets of multiple rows, providing the tally table is big enough. This Tally table will handle up to 1000 entries.

WITH Tally (n) AS
(
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
)
--SELECT * FROM Tally;

Create Table #temp (id int, d datetime, GUID uniqueidentifier, str1 nvarchar(1), number int)

insert into #temp
select n, getdate(), newid(), 'a', 101 from tally 
where N<=100 -- THIS IS WHERE YOU INDICATE HOW MANY ROWS

select * from #temp

Answer 5

I needed to do something similar...inserting ~24,000 records into our warehouse rack system. So..

If you have a table that already has the number of records you're inserting into the new table you can do something like this:

;with t as (
    select top 10000  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) n
    from products -- we really only need 60 but for the purpose of this example...
)
--insert into binlocs (warehouse, zone, row, rack, shelf, bin)
SELECT 101 warehouse, 20 zone, tRow.n row ,tRack.n rack, tShelf.n shelf, tBin.n bin
FROM t tRow
    join t tRack on tRack.n<=case -- how many racks per row
                                    when tRow.n=1 then 37 
                                    when tRow.n=2 then 38 
                                    when tRow.n=3 then 60
                                    when tRow.n=4 then 60 
                                    when tRow.n=5 then 60 
                                    when tRow.n=6 then 40 
                                    when tRow.n=7 then 40 
                                    when tRow.n=8 then 40 
                                    when tRow.n=9 then 38 
                                    when tRow.n=10 then 38 
                                end
    join t tShelf on tShelf.n<7 -- default to 6 shelves per rack
    Join t tBin on tBin.n<10 -- default to 9 bins per shelf
where tRow.n<=10 -- we need 10 rows
order by tRow.n,tRack.n,tShelf.n,tBin.n