How to Insert N Rows of Default Values into a Table

How to insert N rows of default values into a table

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.

Insert multiple rows of default values into a table

If SQL Server 2008+ you can use MERGE for this. Example syntax below.

MERGE INTO SingleIdTable
USING (SELECT *
       FROM   SomeOtherTable
       WHERE  Attribute IS NULL) T
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT
  DEFAULT VALUES
OUTPUT INSERTED.id; 

I'm not sure what practical use this single column table has though?

Multi-row conditional insert with DEFAULT values

Since I couldn't find anything more elegant (as evidenced by the current lack of other answers) I ended up using the suggestion to insert the values into a temporary table first. PostgreSQL makes it really easy to create a "clone" table (using the LIKE keyword) with the same default values but without some of the other unnecessary stuff:

CREATE TEMPORARY TABLE temp_table (
  LIKE my_table INCLUDING DEFAULTS
);

INSERT INTO temp_table (a, b)
VALUES
  ('foo', 'bar'),
  ('foo', DEFAULT),
  (DEFAULT, 'bar');

Then it's just a matter of using the temp table in place of the VALUES clause from before:

INSERT INTO my_table (a, b, datasource)
SELECT a, b, 'my source'
FROM temp_table
WHERE a = 'foo';

One small caveat is that you can't avoid NOT NULL constraints from being copied to the temporary table. For example, if the datasource column was NOT NULL (and didn't have a default value), I would have to add the value (or a placeholder) in the first INSERT query, or drop the column.

Trigger to Insert N number of default values into row

Basically, the Trigger just needs to fill ALL the fields of the new row with the default value except for the one receiving the NEW.user_id.

Assuming that you properly defined default values for each column excepted recommendation_userid, all you have to do is not pass these additional columns when inserting. MySQL will automatically assign the default value to the columns that you did not provide when inserting.

So your INSERT command should just look like:

insert into recommendations_tbl(recommendation_userid) values(EW.user_id);

This will continue to work even if new columns are created in the target table (again, assuming that these new columns have a properly defined default value).

Demo on DB Fiddle:

-- create the table with default values for all columns excepted the primary key
create table recommendations_tbl (
    recommendation_userid integer primary key, 
    recommendation_category varchar(10) default 'diverse',
    recommendation_manufacturer varchar(10) default 'diverse'
);

-- insert a record, providing the primary key only
insert into recommendations_tbl(recommendation_userid) values(1);

-- other columns were assigned default values
select * from recommendations_tbl;

recommendation_userid | recommendation_category | recommendation_manufacturer
--------------------: | :---------------------- | :--------------------------
                    1 | diverse                 | diverse                    

INSERT INTO a table with Computed and Default values

You should always include the columns in your insert statement that you are inserting from your select, otherwise you always have to provide the same number of columns in your insert as your select.

Also if your TableID is autoincrement/identity you do not need to include that.

INSERT INTO TableA (TableBID, Column1, Column2)
SELECT TableAID, Column1, Column2 
FROM TableA_Staging WHERE TableAID > X

Is it possible to insert x number of default values into an empty SQLLite table in a single query?

Having multiple rows with the same values does not make sense in a relational database.

Anyway, another way to get default values for all the actual columns is to insert NULL into the internal rowid column.
To generate rows out of thin air, you need a recursive common table expression:

INSERT INTO MyTable(rowid)
WITH RECURSIVE n(r) AS (
    SELECT NULL
    UNION ALL
    SELECT NULL FROM n
    LIMIT 100000
)
SELECT r FROM n;

Insert multiple rows with default values without using union

Not quite. The problem is what happens when the names are not in the table. You can do this:

insert into user(name, subid)
    select n.name, coalesce(max(u.subid), 1)
    from (select 'bbb' as name union all select 'ccc') n left join
         user u
         on u.name = n.name
    group by u.name;

It still has a union (all) for constructing the names, but the calculation of subid is only expressed once.

When using insert it is a good idea to list the columns explicitly.

MySQL insert row with only default values

This will do it :

INSERT INTO `myTable` (`id`)
VALUES 
    (null),
    (null);
-- or 
INSERT INTO `myTable` () 
VALUES();

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Table1
    (`id` int AUTO_INCREMENT PRIMARY KEY, `title` varchar(5) DEFAULT '***')
;

INSERT INTO Table1
    (`id`, `title`)
VALUES
    (1, 'hi'),
    (2, 'hello')
;

INSERT INTO Table1
    (`id`)
VALUES
    (null),
    (null)
;
INSERT INTO Table1 () VALUES();

Query 1:

SELECT * from Table1

Results:

| id | title |
|----|-------|
|  1 |    hi |
|  2 | hello |
|  3 |   *** |
|  4 |   *** |
|  5 |   *** |

---

Related Topics