Search code examples
sqlsqliteandroid-sqlitesql-insert

How to add specific number of empty rows in sqlite?

I have a SQLite file and I want to add 2550 empty (NULL) rows.

I am able to add one empty line with this code

INSERT INTO my_table DEFAULT VALUES

But I need 2550 rows. Is there any shortcut for it? I don't want to execute same code 2550 times.

Solution

  • If your version of SQLite support it, you could use a recursive CTE to generate a series from 1 to 2550, and then insert "empty" records along that sequence:

    WITH RECURSIVE generate_series(value) AS (
    SELECT 1
    UNION ALL
    SELECT value + 1
    FROM generate_series
    WHERE value + 1 <= 2550
)

INSERT INTO yourTable (col1, col2, ...)
SELECT NULL, NULL, ...
FROM generate_series;

    It is not clear which values, if any, you want to specify for the actual insert. If you omit mention of any column in the insert, then by default SQLite should assign NULL or whatever default value be defined for that column.