accessing 2 databases in 1 sql query

[Mike Bonner]

Does this help?  http://www.sqlitetutorial.net/sqlite-attach-database/

Short version.. attach database lets you attach another db file to the
current connection.  So if you have db "contacts" open, and you attach a
file AS contacts2 (which is an alias defined during the attach) then you
can address a specific table from the first db with "contacts.tablename"
and the second with "contacts2.tablename"

At which point you can most likely do something like..

INSERT INTO CONTACTS2.TABLENAME(fieldname1, fieldname2) SELECT
fieldname1, fieldname2 FROM CONTACTS.TABLENAME;

Alternatively, you might just create a copy of the table from the first db,
to create a table in the second db and then alter the table to add the
missing columns.  Not sure which would be better/more efficient.


On Thu, Jan 18, 2018 at 4:40 AM, Lagi Pittas via use-livecode <
use-livecode at lists.runrev.com> wrote:

> Hi
>
> Sorry if this counts as a double post but I thought I'd cast my net Wieder
> after no bytes in the forums (both puns intended).
>
> https://forums.livecode.com/viewtopic.php?f=12&t=30481
>
> Basically I want to copy (migrate) a table in 1 database to another with
> exactly the same number of records and same primary key but the new table
> has a few extra fields which will be empty.
>
> I can do it with a cut and paste in sqlitestudio - but I've written a
> migration script for all the other tables which are not only smaller  but
> it's a straight insert using a cursor.
>
> I've thought of at least 4 ways of doing this - all will probably be slower
> than this more direct route, but I'm thinking in the future using
> mysql/postgres where I might(will?) need inner or outer joins from external
> databases to do syncing - and yes  know I can do those as well with some
> sql magic but I like SQL 1 liners ever since it was introduced in Foxpro
> (for DOS)  over 25 years ago.
>
> I can do this in Foxpro, VB, PHP  and any myriad of other languages but for
> the life off me I can't think how this can be done using the calling
> methods used within LC, since we pass the database "handle" outside the
> query string how can we qualify a column as to the database it comes from.
>
> If the answer is it can't be done then I'll have to do it in a  slower less
> succinct way.
>
> Thanks Lagi
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>