How do you combine two SQLite databases?

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

How do you combine two SQLite databases?

Neil McLeod
Hello,

I have two SQLite database files, stuff1.db and stuff2.db. Each has three
tables within. I want to combine the two so I have one database file,
stuff.db, with 6 tables. How could I combine the databases? I am aware of
the "attach" command, but this just seemed to create a file with two
sub-databases, each of which has its own tables. (Maybe I used it wrong.) Is
there a specific solution?

Thanks!
Reply | Threaded
Open this post in threaded view
|

Re: How do you combine two SQLite databases?

Kees Nuyt
On Sat, 17 Feb 2007 15:27:25 -0500, you wrote:

>Hello,
>
>I have two SQLite database files, stuff1.db and stuff2.db. Each has three
>tables within. I want to combine the two so I have one database file,
>stuff.db, with 6 tables. How could I combine the databases? I am aware of
>the "attach" command, but this just seemed to create a file with two
>sub-databases, each of which has its own tables. (Maybe I used it wrong.) Is
>there a specific solution?

With the command line program:
sqlite3 database1 .dump >fileall.sql
sqlite3 database2 .dump >>fileall.sql
sqlite3 database3 <fileall.sql

Or even shorter, without intermediate file:
sqlite3 database1 .dump | sqlite database3
sqlite3 database2 .dump | sqlite database3

See also: http://www.sqlite.org/sqlite.html

>Thanks!

Hope this helps.
--
  (  Kees Nuyt
  )
c[_]

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

retrieval speedup help requested

TR Shaw
In reply to this post by Neil McLeod
In sqlite 3 I have two tables. city_loc has 156865 entries and
city_block has 1874352 entries:

CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region
TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT);
CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER
UNSIGNED NOT NULL, loc_id INTEGER NOT NULL);

And my retrieval is but it is slow (6 seconds!):
SELECT cc, region, city, postalCode, lat, lon, areaCode FROM
city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end;

I tried using:
CREATE INDEX city_block_idx ON city_block (start,end);

but it did not appear to speedup anything but it did use up a lot of space.

Any suggestions on how to speedup retrievals?  All help is appreciated.

TIA

Tom


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: retrieval speedup help requested

RB Smissaert
Try running: analyze city_loc
after adding the index.

RBS


-----Original Message-----
From: Tom Shaw [mailto:[hidden email]]
Sent: 17 February 2007 22:16
To: [hidden email]
Subject: [sqlite] retrieval speedup help requested

In sqlite 3 I have two tables. city_loc has 156865 entries and
city_block has 1874352 entries:

CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region
TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT);
CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER
UNSIGNED NOT NULL, loc_id INTEGER NOT NULL);

And my retrieval is but it is slow (6 seconds!):
SELECT cc, region, city, postalCode, lat, lon, areaCode FROM
city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end;

I tried using:
CREATE INDEX city_block_idx ON city_block (start,end);

but it did not appear to speedup anything but it did use up a lot of space.

Any suggestions on how to speedup retrievals?  All help is appreciated.

TIA

Tom


----------------------------------------------------------------------------
-
To unsubscribe, send email to [hidden email]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: retrieval speedup help requested

Anderson, James H (IT)
Where does one get "analyze"?

-----Original Message-----
From: RB Smissaert [mailto:[hidden email]]
Sent: Saturday, February 17, 2007 5:25 PM
To: [hidden email]
Subject: RE: [sqlite] retrieval speedup help requested

Try running: analyze city_loc
after adding the index.

RBS


-----Original Message-----
From: Tom Shaw [mailto:[hidden email]]
Sent: 17 February 2007 22:16
To: [hidden email]
Subject: [sqlite] retrieval speedup help requested

In sqlite 3 I have two tables. city_loc has 156865 entries and
city_block has 1874352 entries:

CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region
TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT);
CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER
UNSIGNED NOT NULL, loc_id INTEGER NOT NULL);

And my retrieval is but it is slow (6 seconds!):
SELECT cc, region, city, postalCode, lat, lon, areaCode FROM
city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end;

I tried using:
CREATE INDEX city_block_idx ON city_block (start,end);

but it did not appear to speedup anything but it did use up a lot of
space.

Any suggestions on how to speedup retrievals?  All help is appreciated.

TIA

Tom


------------------------------------------------------------------------
----
-
To unsubscribe, send email to [hidden email]
------------------------------------------------------------------------
----
-




------------------------------------------------------------------------
-----
To unsubscribe, send email to [hidden email]
------------------------------------------------------------------------
-----
--------------------------------------------------------

NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: retrieval speedup help requested

RB Smissaert
http://www.sqlite.org/lang_analyze.html

RBS


-----Original Message-----
From: Anderson, James H (IT) [mailto:[hidden email]]
Sent: 18 February 2007 01:15
To: [hidden email]
Subject: RE: [sqlite] retrieval speedup help requested

Where does one get "analyze"?

-----Original Message-----
From: RB Smissaert [mailto:[hidden email]]
Sent: Saturday, February 17, 2007 5:25 PM
To: [hidden email]
Subject: RE: [sqlite] retrieval speedup help requested

Try running: analyze city_loc
after adding the index.

RBS


-----Original Message-----
From: Tom Shaw [mailto:[hidden email]]
Sent: 17 February 2007 22:16
To: [hidden email]
Subject: [sqlite] retrieval speedup help requested

In sqlite 3 I have two tables. city_loc has 156865 entries and
city_block has 1874352 entries:

CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region
TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT);
CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER
UNSIGNED NOT NULL, loc_id INTEGER NOT NULL);

And my retrieval is but it is slow (6 seconds!):
SELECT cc, region, city, postalCode, lat, lon, areaCode FROM
city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end;

I tried using:
CREATE INDEX city_block_idx ON city_block (start,end);

but it did not appear to speedup anything but it did use up a lot of
space.

Any suggestions on how to speedup retrievals?  All help is appreciated.

TIA

Tom


------------------------------------------------------------------------
----
-
To unsubscribe, send email to [hidden email]
------------------------------------------------------------------------
----
-




------------------------------------------------------------------------
-----
To unsubscribe, send email to [hidden email]
------------------------------------------------------------------------
-----
--------------------------------------------------------

NOTICE: If received in error, please destroy and notify sender. Sender does
not intend to waive confidentiality or privilege. Use of this email is
prohibited when received in error.

----------------------------------------------------------------------------
-
To unsubscribe, send email to [hidden email]
----------------------------------------------------------------------------
-




-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: retrieval speedup help requested

Mr. Puneet Kishor
In reply to this post by Anderson, James H (IT)
On 2/17/07, Anderson, James H (IT) <[hidden email]> wrote:
> Where does one get "analyze"?
>

man, its pretty much a standard SQL command (at least all the dbs I've
used thus far). Check out http://www.sqlite.org/lang.html. Most
questions answered.



> -----Original Message-----
> From: RB Smissaert [mailto:[hidden email]]
> Sent: Saturday, February 17, 2007 5:25 PM
> To: [hidden email]
> Subject: RE: [sqlite] retrieval speedup help requested
>
> Try running: analyze city_loc
> after adding the index.
>
> RBS
>
>
> -----Original Message-----
> From: Tom Shaw [mailto:[hidden email]]
> Sent: 17 February 2007 22:16
> To: [hidden email]
> Subject: [sqlite] retrieval speedup help requested
>
> In sqlite 3 I have two tables. city_loc has 156865 entries and
> city_block has 1874352 entries:
>
> CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region
> TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT);
> CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER
> UNSIGNED NOT NULL, loc_id INTEGER NOT NULL);
>
> And my retrieval is but it is slow (6 seconds!):
> SELECT cc, region, city, postalCode, lat, lon, areaCode FROM
> city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end;
>
> I tried using:
> CREATE INDEX city_block_idx ON city_block (start,end);
>
> but it did not appear to speedup anything but it did use up a lot of
> space.
>
> Any suggestions on how to speedup retrievals?  All help is appreciated.
>
> TIA
>
> Tom
>
>

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: retrieval speedup help requested

D. Richard Hipp
In reply to this post by TR Shaw
Tom Shaw <[hidden email]> wrote:

> In sqlite 3 I have two tables. city_loc has 156865 entries and
> city_block has 1874352 entries:
>
> CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region
> TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT);
> CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER
> UNSIGNED NOT NULL, loc_id INTEGER NOT NULL);
>
> And my retrieval is but it is slow (6 seconds!):
> SELECT cc, region, city, postalCode, lat, lon, areaCode FROM
> city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end;
>
> I tried using:
> CREATE INDEX city_block_idx ON city_block (start,end);
>

Try CREATE INDEX city_block_idx2 ON city_block(loc_id, start);

--
D. Richard Hipp  <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: How do you combine two SQLite databases?

Neil McLeod
In reply to this post by Kees Nuyt
Thanks! That fixed it.
Neil

On 2/17/07, Kees Nuyt <[hidden email]> wrote:

>
> On Sat, 17 Feb 2007 15:27:25 -0500, you wrote:
>
> >Hello,
> >
> >I have two SQLite database files, stuff1.db and stuff2.db. Each has three
> >tables within. I want to combine the two so I have one database file,
> >stuff.db, with 6 tables. How could I combine the databases? I am aware of
> >the "attach" command, but this just seemed to create a file with two
> >sub-databases, each of which has its own tables. (Maybe I used it wrong.)
> Is
> >there a specific solution?
>
> With the command line program:
> sqlite3 database1 .dump >fileall.sql
> sqlite3 database2 .dump >>fileall.sql
> sqlite3 database3 <fileall.sql
>
> Or even shorter, without intermediate file:
> sqlite3 database1 .dump | sqlite database3
> sqlite3 database2 .dump | sqlite database3
>
> See also: http://www.sqlite.org/sqlite.html
>
> >Thanks!
>
> Hope this helps.
> --
>   (  Kees Nuyt
>   )
> c[_]
>
>
> -----------------------------------------------------------------------------
> To unsubscribe, send email to [hidden email]
>
> -----------------------------------------------------------------------------
>
>


--
Ubuntu Linux 6.06 (Dapper Drake)