How to insert N rows of default values into a table

How to insert N rows of default values into a table

sql insert multiple rows with same value
sql insert multiple rows from another table
sql insert multiple rows from select
how to insert multiple values in a single column in sql
how to insert multiple values in a single column in mysql
mysql insert multiple rows from select
how to insert multiple rows in mysql using loop
php mysql insert multiple rows at once

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?


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.

Db2 12 - Application programming and SQL - Inserting rows by , One way to insert data into tables is to use the SQL INSERT statement. This method is In an application program, specify arrays of column values to insert multiple rows into a table. Use host ROWID columns always have default values. SQL provides the INSERT statement that allows you to insert one or more rows into a table. The INSERT statement allows you to: Insert a single row into a table. Insert multiple rows into a table. Copy rows from a table to another table. We will examine each function of the INSERT statement in the following sections.


An easier way is:

insert dbo.OrderStatus default values
go 500

this will insert 500 rows of default values.

SQLite Insert Into - Inserting New Rows Into a Table, SQLite provides various forms of the INSERT statements that allow you to insert a single row, multiple rows, and default values into a table. In addition, you can  Then, I attempt to insert a single row into the table with default values for all the columns: INSERT INTO #Table DEFAULT VALUES Rather than success, I get the following error: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#Table___0001A'; column does not allow nulls. INSERT fails.


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

SQL Server INSERT Multiple Rows Into a Table Using One Statement, Summary: in this tutorial, you will learn how to insert multiple rows into a table using a In this syntax, instead of using a single list of values, you use multiple 2) DEFAULT 0, start_date DATE NOT NULL, expired_date DATE NOT NULL );  1. When you want to insert default values into a table, some databases allow this syntax: INSERT INTO table DEFAULT VALUES; ASE does not support this. Using: INSERT INTO table (col2, col3) VALUES (DEFAULT, DEFAULT) and skipping the identity column works for columns with constant default values, but not for computed columns including timestamp.


Set up a trigger when a new row is CREATEd:

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

SQL Server INSERT: Adding a Row Into a Table By Practical , To add one or more rows into a table, you use the INSERT statement. The default value if the column has a default value specified. To capture inserted values from multiple columns, you specify the columns in the output as shown in the  Summary: in this tutorial, you will learn how to use SQL INSERT statement to insert data into tables. The INSERT statement inserts one or more rows into a table. The INSERT statement is sometimes referred to as an INSERT INTO statement. SQL INSERT statement – insert one row into a table. The following illustrates the INSERT statement that


MySQL INSERT, To insert multiple rows into a table using a single INSERT statement, you use the following 2) MySQL INSERT – Inserting rows using default value example. In the previous tutorial, you have learned how to add one row at a time to a table by using the INSERT statement. To add multiple rows to a table at once, you use the following form of the INSERT statement: INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2), (value_list_n);


INSERT (Transact-SQL), The row constructor consists of a single VALUES clause with multiple D. Inserting data into a table with columns that have default values. Here Mudassar Ahmed Khan has explained with an example, how to add (insert) Column with Default Value to DataTable in C# and VB.Net. When Column is specified a Default Value then if its value is not passed i.e. if the value is NULL or Nothing then in such scenario the Default Value is set for that column. TAGs: C#.Net, VB.Net, DataTable


INSERT statement, Single-row and multiple-row VALUES expressions can include the keyword DEFAULT. Another way to insert the default value into the column is to omit the column from the column list and only insert values into other columns in the table. Specifying the blocked form of the INSERT statement to add multiple rows. For every row you insert, you must supply a value for each column defined with the NOT NULL attribute if that column does not have a default value. The INSERT statement for adding a row to a table or view may look like this: INSERT INTO table-name (column1, column2,