On Mon, 6 Mar 2006, Dale Qualls wrote:
>Okay, here's a strange one. I have sqlite dbs for every day of the week.
>Every db is the same (contains 4 tables) as far as structure goes.
>
>Is there a method from the command line (on a win32 box) that I can use
>the sqlite.exe to merge 10 days (so 10 dbs) worth of data into a single
>"master" database? I was accomplishing this with 40 separate queries (4
>for each of the 10 days) within Access 2k but it takes an ungodly amount
>of time.
>
>Just wondering if it was possible from the cmd line to speed it up. A
>vacuum takes a large amount of time as well, each of the database files
>range from 800-1200MB.
>
>This is 2.x by the way.
You can generate a file of inserts using a script. Something like this in
a file script:
.mode insert table1 table2 table3 table4
select * from table1;
...
select * from table4;
For each database, run:
C:\dir> sqlite.exe day1.db < script > day1.inserts
...
C:\dir> sqlite.exe day10.db < script > day10.inserts
C:\dir> type day1.inserts ... day10.inserts | sqlite.exe master.db
You'd want to change the last command to include a "BEGIN;" before the
inserts and a "COMMIT;" after the inserts, but my windows command line
knowledge is limited.
If you want a single invocation of sqlite.exe, you can "ATTACH" the
different days and transfer data from each directly:
ATTACH "day1.db" AS day;
BEGIN;
INSERT INTO table1 SELECT * FROM day.table1;
...
INSERT INTO table4 SELECT * FROM day.table4;
COMMIT;
DETACH day;
...
ATTACH "day10.db" AS day;
BEGIN;
INSERT INTO table1 SELECT * FROM day.table1;
...
INSERT INTO table4 SELECT * FROM day.table4;
COMMIT;
DETACH day;
With scripting, this script can be generated from a list of files, and fed
to sqlite.exe using a pipe. It should be as quick as it gets.
>
>Any help would be most appreciated. The archives didn't lend much help.
>
>ciao!
>
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
