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.
Any help would be most appreciated. The archives didn't lend much help.
ciao!
**************************************************************************
The preceding message and any attachments may contain confidential
information protected by the attorney-client or other privilege. If you
believe that it has been sent to you in error, please reply to the sender
that you received the message in error and then delete it. Nothing in
this email message, including the typed name of the sender and/or this
signature block, is intended to constitute an electronic signature unless
a specific statement to the contrary is included in the message.
**************************************************************************
Dale
You should be able to do this with a simple sql script file. Take the
commands below, change the declarations of the tables to match your four
tables, and the names of the database files to match your files, then
save the result as "merge.sql". These sql commands attach to each of
your daily databases, drop and recreate each of your four tables, then
populate the tables in the master database with all the records from the
daily databases.
attach database 'day1.db' as day1;
attach database 'day2.db' as day2;
attach database 'day3.db' as day3;
attach database 'day4.db' as day4;
attach database 'day5.db' as day5;
attach database 'day6.db' as day6;
attach database 'day7.db' as day7;
attach database 'day8.db' as day8;
attach database 'day9.db' as day9;
attach database 'day10.db' as day10;
drop table if exists tab1;
drop table if exists tab2;
drop table if exists tab3;
drop table if exists tab4;
create table tab1(...);
create table tab2(...);
create table tab3(...);
create table tab4(...);
insert into tab1
select * from day1.tab1
union select * from day2.tab1
union select * from day3.tab1
union select * from day4.tab1
union select * from day5.tab1
union select * from day6.tab1
union select * from day7.tab1
union select * from day8.tab1
union select * from day9.tab1
union select * from day10.tab1;
insert into tab2
select * from day1.tab2
union select * from day2.tab2
union select * from day3.tab2
union select * from day4.tab2
union select * from day5.tab2
union select * from day6.tab2
union select * from day7.tab2
union select * from day8.tab2
union select * from day9.tab2
union select * from day10.tab2;
insert into tab3
select * from day1.tab3
union select * from day2.tab3
union select * from day3.tab3
union select * from day4.tab3
union select * from day5.tab3
union select * from day6.tab3
union select * from day7.tab3
union select * from day8.tab3
union select * from day9.tab3
union select * from day10.tab3;
insert into tab4
select * from day1.tab4
union select * from day2.tab4
union select * from day3.tab4
union select * from day4.tab4
union select * from day5.tab4
union select * from day6.tab4
union select * from day7.tab4
union select * from day8.tab4
union select * from day9.tab4
union select * from day10.tab4;
Now at the command prompt you can open your master database and execute
the merge with the following command.
sqlite master.db '.read merge.sql'
HTH
Dennis Cote
