Do you mean something like this, perchance? This sets .mode insert <tablename>
then does a select ..., which outputs the selected data in the form of INSERT
statements into a table called <tablename>. The .mode list just allows other
commands to be added to the file ...
.output aesodata.sql
.mode list
select 'BEGIN;';
.mode insert Interval
select *
from Interval
order by Interval;
.mode list
select 'COMMIT;';
select 'BEGIN;';
.mode insert Actual
select *
from Actual
order by Interval;
.mode list
select 'COMMIT;';
select 'BEGIN;';
.mode insert Forecast
select *
from Forecast
order by Interval, Projected;
.mode list
select 'COMMIT;';
select 'VACUUM;';
select 'ANALYZE;';
which produces (elided output) like follows:
BEGIN;
INSERT INTO Interval VALUES(1996,9,30,'24',234486);
INSERT INTO Interval VALUES(1996,10,1,'01',234487);
INSERT INTO Interval VALUES(1996,10,1,'02',234488);
INSERT INTO Interval VALUES(1996,10,1,'03',234489);
INSERT INTO Interval VALUES(1996,10,1,'04',234490);
INSERT INTO Interval VALUES(1996,10,1,'05',234491);
INSERT INTO Interval VALUES(1996,10,1,'06',234492);
INSERT INTO Interval VALUES(1996,10,1,'07',234493);
INSERT INTO Interval VALUES(1996,10,1,'08',234494);
INSERT INTO Interval VALUES(1996,10,1,'09',234495);
...
COMMIT;
BEGIN;
INSERT INTO Actual VALUES(234487,1325513676,13.5,4998);
INSERT INTO Actual VALUES(234488,1325513676,13.5,4891);
INSERT INTO Actual VALUES(234489,1325513676,12.0,4831);
INSERT INTO Actual VALUES(234490,1325513676,10.5,4791);
INSERT INTO Actual VALUES(234491,1325513676,10.5,4778);
INSERT INTO Actual VALUES(234492,1325513676,13.5,4862);
INSERT INTO Actual VALUES(234493,1325513676,15.63,5173);
INSERT INTO Actual VALUES(234494,1325513676,19.65,5624);
INSERT INTO Actual VALUES(234495,1325513676,21.0,5773);
INSERT INTO Actual VALUES(234496,1325513676,21.0,5848);
...
COMMIT;
BEGIN;
INSERT INTO Forecast VALUES(234487,234486,1325513676,13.5,4911);
INSERT INTO Forecast VALUES(234488,234487,1325513676,13.5,4771);
INSERT INTO Forecast VALUES(234489,234488,1325513676,10.5,4732);
INSERT INTO Forecast VALUES(234490,234489,1325513676,10.5,4706);
INSERT INTO Forecast VALUES(234491,234490,1325513676,10.5,4730);
INSERT INTO Forecast VALUES(234492,234491,1325513676,13.5,4828);
INSERT INTO Forecast VALUES(234493,234492,1325513676,15.88,5165);
INSERT INTO Forecast VALUES(234494,234493,1325513676,16.3,5569);
INSERT INTO Forecast VALUES(234495,234494,1325513676,21.0,5761);
INSERT INTO Forecast VALUES(234496,234495,1325513676,21.0,5855);
...
COMMIT;
VACUUM;
ANALYZE;
> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Luca Ferrari
> Sent: Wednesday, 23 March, 2016 03:50
> To: SQLite
> Subject: [sqlite] dump only data, change schema, reload
>
> Hi all,
> I've a few hundreds sqlite3 database files, all almost equals except
> for some constraint that has changed during time.
> As I know, there is no way to alter constraint (e.g., unique indexes),
> and therefore I have to migrate data to a new schema version.
> That is possible because data will fit into the same table schema, but
> I'm looking for a way to do it massively (and remotely).
> So far I was thinking to wrap a shell script to dump the content of a
> single database, create the new schema and reload the dump in it.
> The problem is that .dump provides data and schema, while I'd like to
> have data only.
> Other commands like .clone and .backup works pretty much the same, as
> far as I understand.
>
> This leads me to either use awk/sed to manipulate the dump or to
> hard-code single select statements into the script to extract data.
> Is there any smarter way to dump only data in a loadable form?
>
> Thanks
> Luca
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
