On 11 Oct 2012, at 3:07pm, 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
> 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
