Re: [sqlite] Best way of merging tables

[Kees Nuyt]

On Mon, 31 Dec 2007 09:56:23 +0100, "Mag. Wilhelm Braun"
<[EMAIL PROTECTED]> wrote:

>hi,
>
>I have following situation:
>
>database2006: table 'myname': Columns: "ID integer primary key, 
>timestamp integer, x text, y text
>database2007: table 'myname': Columns: "ID integer primary key, 
>timestamp integer, x text, y text
>
>empty
>comvineddatabase: : table 'myname': Columns: "ID integer primary key, 
>timestamp integer, x text, y text
>
>I would like do have a combined database table 'myname': Columns: "ID 
>integer primary key, timestamp integer, x text, y text
>where I insert first the columns of database2006, and afterwards 
>database2007.
>
>at the moment I do something like this:
>
>*attach: database2006:*
>
>select timestamp, x , y from database2006
>
>and for eachrow in selectionresult
>
>insert into comvineddatabase (timestamp, x , y) values(?,?,?)
>
>*after that the same for database2006.*
>
>select timestamp, x , y from database2006
>
>and for eachrow in selectionresult
>
>insert into comvineddatabase (timestamp, x , y) values(?,?,?)
>
>
>I was wondering if there is not a more effective way of doing that: 
>maybe even within a single SQL statement?

Sure there is, using the "INSERT INTO / SELECT" syntax on
http://www.sqlite.org/lang_insert.html

There are several possibilities, here is an (unteste3d) example:

(open comvineddatabase)
(create tables as needed)
ATTACH DATABASE 'database2006' AS d2006;
INSERT INTO myname (timestamp, x, y) 
        SELECT timestamp, x, y FROM d2006.myname;
DETACH DATABASE d2006;
ATTACH DATABASE 'database2007' AS d2007;
INSERT INTO myname (timestamp, x, y) 
        SELECT timestamp, x, y FROM d2007.myname;
DETACH DATABASE d2007;

If the table structures are exactly the same, the INSERT
statement can even be shortened:
INSERT INTO myname SELECT * FROM d2006.myname;
etc.


>Thanks in advance W.Braun

HTH
-- 
  (  Kees Nuyt
  )
c[_]

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------