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?

Pyrography

what are you achieving by removing default GETDATE() – Marquise

What's the purpose here - why do you need 3 "default" records? – Viscometer

@Marquise Actually, my mind bugged on that one. I'm not achieving anything. Although, I'm still interested in the INSERT INTO DEFAULT VALUES equivalent for multiple rows. – Pyrography

@DStanley I know that seems strange, but I want to create a table for the order statuses and then create a junction table between the orderStatus and language tables, which will contain the translations for each status. So, I already know that I need 5 status records for now and then the admins will be able to add additional ones from the app. – Pyrography

@BishopBarber Yes that does seem a bit strange, but it may make your job easier - could you not just add a record relating to each language rather than "default" records? – Viscometer

@DStanley A record relating to each language will be added in the table OrderStatusLang (junction table). The OrderStatus table only contains the very basic information of a status. The reason I didn't put it all in one single table is that it makes it easier to create a procedure which will return the statuses depending on the selected language in the application. – Pyrography

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.

Juvenility

Answer 2

An easier way is:

insert dbo.OrderStatus default values
go 500

this will insert 500 rows of default values.

Bullnecked

I'm trying to make the inserts inside of a BEGIN .. END block. Is there a way to use your solution in that case or (should I / must I) stick with the while loop? – Pyrography

'Go' is a SSMS command and not part of SQL or TSQL.This means that you cannot use it as part of a proc or script. – Bullnecked

This is easy if you're just doing it in SSMS or SqlCmd. You can't use it in a SP or function. – Juvenility

@benjaminmoskovits Could you specify it in your answer? It might be clearer for newcomers, since my question doesn't include the SSMS tag. – Pyrography

When you are working in SQL Server Management Studio in a query window you are in 'SSMS' mode and Go is available to you. It is interpeted by SSMS. If you write a stored procedure it is often referred to as a 'proc' and when the SQL Server engine encounters GO it will probably throw an error. – Bullnecked

Answer 3

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

Afterglow

Answer 4

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

Dunkle

Answer 5

Set up a trigger when a new row is CREATEd:

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

Alduino

This method is not efficient (recursion), and somewhat constrained to always adding the same number of rows, and would require a batch column key for termination of the trigger loop. However, +10 for creativity! – Afterglow