insert multiple rows in sqlite (with autoincrementing id numbers)

Question

I was most pleased to find this technique, but using it seems to prevent me from having auto-incrementing id values in the rows.

Is the only way to have id values to type them in the lines myself?

$db.execute("
              INSERT INTO 'students'
              SELECT 'Brett' AS 'first_name', 'Iton' AS 'last_name', 'M' AS 'gender', '1966-01-17' AS 'birthday', '415-879-54451' AS 'phone', DATETIME('now') AS 'created_at', DATETIME('now') AS 'updated_at'
        UNION SELECT 'Lady','Gaga', 'F', '1970-03-17', '213-879-4545', DATETIME('now'), DATETIME('now')
        UNION SELECT 'Former','Prince', 'F', '1966-04-17', '321-879-4545', DATETIME('now'), DATETIME('now')
            ")

added code, but there is no way I see to specify autoincrementation... — dwilbank, Mar 23 '14 at 22:33

score 2 · Accepted Answer · answered Mar 24 '14 at 07:31

2

An autoincrementing column gets an auto-incremented value when you insert nothing or NULL into it.

Either set the ID column to NULL:

INSERT INTO students
SELECT NULL, 'somebody', 'z'
...

Or specify some columns, without the ID column:

INSERT INTO students(name, gender)
SELECT 'somebody', 'z'
...

answered Mar 24 '14 at 07:31

CL.

173,858
17
217
259

I now have autoincrementing columns! – dwilbank Mar 24 '14 at 14:19

knut · Answer 2 · 2014-03-24T11:54:35.207

You flagged your question with ruby, so a solution with sequel (*) may help you:

(*) Remark: sequel loads sqlite3 in background.

require 'sequel'
DB = Sequel.sqlite
DB.create_table(:tab1){
  primary_key :id
  field :a, :type => :nvarchar, :size => 10
  field :b, :type => :nvarchar, :size => 10
}

DB[:tab1].multi_insert([
  { :a => 'a1', :b => 'b1'},
  { :a => 'a2', :b => 'b2'},
  { :a => 'a3', :b => 'b3'},
])

puts DB[:tab1].all

The output:

{:id=>1, :a=>"a1", :b=>"b1"}
{:id=>2, :a=>"a2", :b=>"b2"}
{:id=>3, :a=>"a3", :b=>"b3"}

If you prefer raw SQL you can use:

require 'sequel'
DB = Sequel.sqlite

DB.create_table(:students){
  primary_key :id
  field :first_name, :type => :nvarchar, :size => 10
  field :last_name, :type => :nvarchar, :size => 10
  field :gender, :type => :nvarchar, :size => 1
  field :birthday, :type => :date
  field :phone, :type => :nvarchar, :size => 10
  field :created_at, :type => :date
  field :updated_at, :type => :date
}

  DB.execute("
  INSERT INTO 'students'
    ('first_name', 'last_name', 'gender', 'birthday', 'phone', 'created_at', 'updated_at')
  VALUES
    ('Brett', 'Iton', 'M', '1966-01-17', '415-879-54451', DATETIME('now'), DATETIME('now')),
    ('Lady','Gaga', 'F', '1970-03-17', '213-879-4545', DATETIME('now'), DATETIME('now')),
    ('Former','Prince', 'F', '1966-04-17', '321-879-4545', DATETIME('now'), DATETIME('now'))
  ")

puts DB[:students].all

Please note: the maximum number of rows in one VALUES clause is 1000 - but I think that's no problem for you.

insert multiple rows in sqlite (with autoincrementing id numbers)

2 Answers2