Thanks a lot Kees Nuyt,
greate help
W.Braun
Kees Nuyt wrote:
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
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------