accessing 2 databases in 1 sql query

[Lagi Pittas]

Hi Thanks MIke

Your a saviour - I read about attach over a year or so  ago when I was
looking at how pragma works and other stuff like that but had no need till
now so totally forgot .

Thanks again

Lagi



On 18 January 2018 at 14:14, Mike Bonner via use-livecode <
use-livecode at lists.runrev.com> wrote:

> 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
> >
> _______________________________________________
> 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
>