How to add specific number of empty rows in sqlite?

user_14341558 0
Viewed 778

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.

sql-insert android-sqlite sqlite sql
3 Answers

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.

user_18632290

If your table is empty, then use a recursive CTE to get 2550 rows each consisting of the integers 1 to 2550 and use them to insert 2550 rows:

WITH cte AS (
  SELECT 1 nr
  UNION ALL
  SELECT nr + 1
  FROM cte
  WHERE nr < 2550
)
INSERT INTO my_table(rowid)
SELECT nr FROM cte

This way, you use the column rowid where the integer values of the CTE will be stored and there is no need to enumerate all the columns of your table in the INSERT statement.
These columns will get their default values.

If your table is not empty you can do it in a similar way by starting the integer numbers from the max rowid value in the table +1:

WITH cte AS (
  SELECT MAX(rowid) + 1 nr FROM my_table
  UNION ALL
  SELECT nr + 1
  FROM cte
  WHERE nr < (SELECT MAX(rowid) + 2550 FROM my_table)
)
INSERT INTO my_table(rowid)
SELECT nr FROM cte

See a simplified demo (for 5 rows).

But since you also tagged android-sqlite you can use a for loop:

for (int i = 1; i <= 2550; i++) {
    db.execSQL("INSERT INTO my_table DEFAULT VALUES");
}

where db is a valid non null instance of SQLiteDatabase.

user_104988280

You can generate numbers using a recursive CTE and then insert . . . but you need to be more explicit about the values being inserted:

with cte as (
      select 1 as n
      union all
      select n + 1
      from cte
      where n < 2550
     )
insert into mytable (<something>)
    select <something>
    from cte;

I think you need to specify the value for at least one column in SQLite.

user_11440350
Related