CODEWITHSUNDEEP
rubysqlite
dwilbank
dwilbank

Reputation: 2520

insert multiple rows in sqlite (with autoincrementing id numbers)

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')
            ")

Upvotes: 1

Views: 367

Answers (2)

knut
knut

Reputation: 27875

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.

Upvotes: 1

CL.
CL.

Reputation: 180192

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'
...

Upvotes: 2

Related Questions