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 "all\tab_X.db" AS db1;
ATTACH "all\tab_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

Table DB Y

Pmt_ID	P_CUSTOMER_NAME	P_CASH_AMOUNT
1	ZNZ MINI MARKET	338.14
2	SEMANGAT SDN BHD	298.92
3	TAFFANY MARKETING	253.34

Result I want

PK	Pmt_ID	P_CUSTOMER_NAME	P_CASH_AMOUNT
X1	1	ABC ENTERPRISE	1035.18
X2	2	RAS HOLDING	385.19
X3	3	SEMANGAT BHD	622.21
Y1	1	ZNZ MINI MARKET	338.14
Y2	2	SEMANGAT SDN BHD	298.92
Y3	3	TAFFANY MARKETING	253.34

Hmm..How do I create a new table in a new db file from these two tables — Pavitran, Jan 22 '21 at 09:12
Post sample data of the 2 existing tables and the expected result of the new table to clarify what you want. — forpas, Jan 22 '21 at 09:15

score 1 · Accepted Answer · answered Jan 22 '21 at 09:55

First attach the 3 databases:

ATTACH "all\tab_X.db" AS db1;
ATTACH "all\tab_Y.db" AS db2;
ATTACH "all\tab_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

SQLITE: Merge tables from different databases with Primary Key Constraint

1 Answers1