Working with multiple databases

sqlite

Using sqlite3 on Linux, how can one work with multiple databases?

I'd like to do something like the following in database 1?

sqlite3 database1.db
insert into database1.mytable values (select * from database2.mytable)

How do I write the SELECT query for database2 above?

Best Answer

(reposting swasheck's comment as reply)

sqlite3 database1.db
> ATTACH DATABASE 'database2.db' AS database2;
> INSERT INTO mytable SELECT * FROM database2.mytable;

Related Solutions

Select column from ALL tables

You're looking for UNION and UNION ALL.

select my_column from table1
union all
select my_column from table2
union all
select my_column from ...

But needing to select one column from every table is likely the sign of a significant design error.

How to optimize query

It's not clear what you want but this may be it:

SELECT
    Name,
    SUM(CASE WHEN Used IS NULL 
           THEN ItemQty 
           ELSE 0 
        END) AS QtyNotUsed
FROM 
    ContainersReceived
GROUP BY
    Name ;

To be efficient, add an index on (Name, Used, ItemQty)

If you have another index, on (Used, Name, ItemQty), this query will be even more efficient - but it will not show Names that have no row with Used IS NULL:

SELECT
    Name,
    SUM(ItemQty) AS QtyNotUsed
FROM 
    ContainersReceived
WHERE
    Used IS NULL 
GROUP BY
    Name ;

Best Answer

Related Solutions

Select column from ALL tables

How to optimize query

Related Question