Re: [sqlite] primary key with bulk insert (UNION SELECT)

[Alan Frankel]

I ended up modifying the statement to add NULL to the ROWID column for each 
row. SQLite silently replaces the NULL with an automatically generated row id. 
This works even in older versions, so this was the solution I ended up using.

Thanks,
Alan

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Davies
Sent: Friday, October 12, 2012 8:42 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] primary key with bulk insert (UNION SELECT)

On 11 October 2012 15:07, Alan Frankel <alan.fran...@mathworks.com> wrote:
> I have a table that uses an autogenerated id as primary key. I want to do 
> bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I 
> specify an id for each row:
>
> sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name 
> sqlite> VARCHAR(25), distance REAL); insert into CelestialObject 
> sqlite> select 'Betelguese' as name, 200 as distance UNION SELECT 
> sqlite> 'Procyon', 500;
> Error: table CelestialObject has 3 columns but 2 values were supplied
>
> If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY 
> AUTOINCREMENT") when I create the table, the error is the same. Can anyone 
> tell me whether there's a way to use a bulk insert without specifying an id 
> for each row?

insert into CelestialObject( name, distance ) select 'Betelguese' as name, 200 
as distance UNION SELECT 'Procyon', 500;

>
> Thanks,
> Alan
>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] primary key with bulk insert (UNION SELECT)

[Simon Davies]

On 11 October 2012 15:07, Alan Frankel  wrote:
> I have a table that uses an autogenerated id as primary key. I want to do 
> bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I 
> specify an id for each row:
>
> sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name 
> VARCHAR(25), distance REAL);
> sqlite> insert into CelestialObject select 'Betelguese' as name, 200 as 
> distance UNION SELECT 'Procyon', 500;
> Error: table CelestialObject has 3 columns but 2 values were supplied
>
> If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY 
> AUTOINCREMENT") when I create the table, the error is the same. Can anyone 
> tell me whether there's a way to use a bulk insert without specifying an id 
> for each row?

insert into CelestialObject( name, distance ) select 'Betelguese' as
name, 200 as distance UNION SELECT 'Procyon', 500;

>
> Thanks,
> Alan
>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] primary key with bulk insert (UNION SELECT)

[Simon Slavin]

On 11 Oct 2012, at 3:07pm, Alan Frankel  wrote:

> I have a table that uses an autogenerated id as primary key. I want to do 
> bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I 
> specify an id for each row:
> 
> sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name 
> VARCHAR(25), distance REAL);

Note that SQLite has no VARCHAR type and no limit to field length.

> sqlite> insert into CelestialObject select 'Betelguese' as name, 200 as 
> distance UNION SELECT 'Procyon', 500;

There's no SELECT after UNION.  But even then that format for the INSERT 
command is slow when you actually know the values you want to use.  Use this 
instead:

INSERT INTO CelestialObject (name, distance) VALUES ('Betelguese', 200), 
('Procyon', 500);

simon$ sqlite3 ~/Desktop/fred.sqlite
SQLite version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name TEXT, 
distance REAL);
sqlite> INSERT INTO CelestialObject (name, distance) VALUES ('Betelguese', 
200), ('Procyon', 500);
sqlite> SELECT * FROM CelestialObject;
1|Betelguese|200.0
2|Procyon|500.0

Note that even this format was implemented only in recent versions of SQLite.  
If it doesn't work in your version tell us which version you're using.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] primary key with bulk insert (UNION SELECT)

[Alan Frankel]

I have a table that uses an autogenerated id as primary key. I want to do bulk 
inserts using UNION SELECT, but it doesn't seem to be happy unless I specify an 
id for each row:

sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name VARCHAR(25), 
distance REAL);
sqlite> insert into CelestialObject select 'Betelguese' as name, 200 as 
distance UNION SELECT 'Procyon', 500;
Error: table CelestialObject has 3 columns but 2 values were supplied

If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY 
AUTOINCREMENT") when I create the table, the error is the same. Can anyone tell 
me whether there's a way to use a bulk insert without specifying an id for each 
row?

Thanks,
Alan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users