sql - SQLITE: Merge tables from different databases with Primary Key Constraint

Question

I want to concatenate/append tables from 2 different databases (database X and Y).

ATTACH "allab_X.db" AS db1;
ATTACH "allab_Y.db" AS db2;

INSERT INTO db1.PAYMENT SELECT * FROM db2.PAYMENT

However this is giving me this error: UNIQUE constraint failed: PAYMENT.ID

How do I create a new column that combines the ID with db name (eg: X_123, Y_123) and make that the primary key?

Table DB X

Pmt_ID	P_CUSTOMER_NAME	P_CASH_AMOUNT
1	ABC ENTERPRISE	1035.18
2	RAS HOLDING	385.19
3	SEMANGAT BHD	622.21

question from:https://stackoverflow.com/questions/65841685/sqlite-merge-tables-from-different-databases-with-primary-key-constraint

Answer 1

First attach the 3 databases:

ATTACH "allab_X.db" AS db1;
ATTACH "allab_Y.db" AS db2;
ATTACH "allab_Z.db" AS db3;

Create the new table:

CREATE TABLE db3.PAYMENT(Pmt_ID TEXT PRIMARY KEY, P_CUSTOMER_NAME TEXT, P_CASH_AMOUNT REAL);

Finally insert the rows:

INSERT INTO db3.PAYMENT(Pmt_ID, P_CUSTOMER_NAME, P_CASH_AMOUNT) 
SELECT 'X' || Pmt_ID, P_CUSTOMER_NAME, P_CASH_AMOUNT FROM db1.PAYMENT
UNION ALL
SELECT 'Y' || Pmt_ID, P_CUSTOMER_NAME, P_CASH_AMOUNT FROM db2.PAYMENT