Inserting multiple rows in table

(1) By Rich S (richs) on 2020-04-03 15:41:43 [source]

Running SQLite version 3.31.1 on Slackware-14.2 and trying to insert 2860
rows in the table. Each row has 12 fields, one for each column in the table,
yet sqlite3 complains:

$ sqlite3 testing.db < biota.sql
Error: near line 1: all VALUES must have the same number of terms

The first two lines of the file (wrapped to fit in here) are:

insert into Biota (sampid,site_id,sampdate,tclass,torder,tfamily,tgenus,
tspecies,subspecies,common_name,ffg,quant) values 

(1,11,'2000-07-18','Annelida','Oligochaeta','Tubificidae',null,null,null,null,
'Gatherer',22),

Twelve columns specified on the first line and 12 values presented in each
following line. (That's been checked.)

What am I missing?

Rich

(2) By TripeHound on 2020-04-03 15:57:37 in reply to 1 [link] [source]

Just to double-check: does the last line of values end with a semicolon (;) instead of a comma (,)?

(3) By Rich S (richs) on 2020-04-03 16:05:56 in reply to 2 [link] [source]

Yep. It does.

I thought the problem was not quoting the column names in the first line of the INPUT statement file, but that's not the problem. There are the same number of terms (12) in all rows.

Thanks

(5) By Rich S (richs) on 2020-04-03 16:17:59 in reply to 3 [link] [source]

Ha! Found the problem: the beginning of line 38 had a brace '{' rather than a parenthesis '('.

(4) By Larry Brasfield (LarryBrasfield) on 2020-04-03 16:09:19 in reply to 1 [link] [source]

When I create a table with the same columns you show, in SQLite 3.30.1, (which came with SQLitespeed for Windows), then run your insert DML without that trailing comma, it succeeds.

Since I highly doubt any bug this fundamental would get by the pre-release testing, I think you must be missing something we forum participants must also be missing. Can you reduce the problem to something shorter, so that you can show us some input to the sqlite3 shell that fails, without need of formatting?

(6) By Rich S (richs) on 2020-04-03 16:48:58 in reply to 4 [link] [source]

Larry,

The problem is not a brace rather than a parenthesis, it's the limit on the number of rows that 3.31.1 accepts, about 4.

When I try to insert more than 4-5 rows I get the error of a problem near line 1 near '('. But, when I enter have a single line, or a couple of lines, there's no problem. I discovered this when I tried populating the Sites table with 61 rows of data. Each time I had more than about 4 rows (and I don't remember the specific number of rows) the error was generated. When I cut it back to 1-3 rows they were inserted with no problems. And, each line but the last terminates with a comma.

So, now on my large data files I'll make each row a separate insert into statement. But, that's after a 1.5 hour conference call I have coming up.

Thanks

(7) By Stephan Beal (stephan) on 2020-04-03 17:12:28 in reply to 6 [link] [source]

It sounds like you are pasting it into the shell and maybe hitting a line-reading buffer limit. You can pipe the whole content into the shell in a single go to bypass any such limit.

(8) By Larry Brasfield (LarryBrasfield) on 2020-04-03 17:19:59 in reply to 7 [link] [source]

From the OP's "$ sqlite3 testing.db < biota.sql", I gather that he was at least redirecting input. That should be no more limiting than an inter-process pipe.

(9) By Keith Medcalf (kmedcalf) on 2020-04-03 17:37:29 in reply to 1 [link] [source]

Don't use redirection.

Use the .read command.

That is:

sqlite3 testing.db
.read biota.sql

You will like the error messages much better than when you "type in" the contents of the file at the keyboard.

(10) By Rich S (richs) on 2020-04-03 19:33:11 in reply to 1 [link] [source]

Issue resolved. There were 'not null' column constraint violations toward the end of the 2860 row data file. Modifying the file so each line is a separate 'insert into ...' statement resulted in sqlite printing the row number and specific error rather than the above.

Fixed the errors, deleted table rows, and re-read the data table via indirection. All's well now.

Thanks everyone for your comments.

Stay healthy.

(11) By David Raymond (dvdraymond) on 2020-04-06 12:26:04 in reply to 1 [link] [source]

One other note on using the CLI and .read is that you can use the .bail option of the CLI to make it an all-or-nothing import if that's important.

So my file would look like:
.bail on
begin;
<inserts, updates, etc>
commit;

With ".bail on" set and the transaction in there, then .read will stop at the first error and it won't commit it all.

Depending on how complex your statements are and what was in your database to begin with, cleaning up an attempt which stopped with an error so that you can try again might be complicated, annoying, or impossible.