PRAGMA foreign_keys = on;
ATTACH DATABASE 'db1.sqlite' AS db1;
ATTACH DATABASE 'db2.sqlite' AS db2;
BEGIN;
CREATE TABLE Fruit (
id INTEGER PRIMARY KEY NOT NULL,
name TEXT UNIQUE ON CONFLICT IGNORE
)
;
CREATE TABLE Juice (
id INTEGER PRIMARY KEY NOT NULL,
name TEXT UNIQUE ON CONFLICT IGNORE
)
;
CREATE TABLE Recipe (
id INTEGER PRIMARY KEY NOT NULL,
juice_id INTEGER NOT NULL,
fruit_id INTEGER NOT NULL,
FOREIGN KEY ( juice_id ) REFERENCES Juice ( id )
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY ( fruit_id ) REFERENCES Fruit ( id )
ON UPDATE CASCADE
ON DELETE CASCADE
)
;
INSERT INTO Fruit ( id, name ) SELECT id, name FROM db1.Fruit;
INSERT INTO Juice ( id, name ) SELECT id, name FROM db1.Juice;
INSERT INTO Recipe ( id, juice_id, fruit_id ) SELECT id, juice_id, fruit_id FROM db1.Recipe;
INSERT INTO Fruit ( name ) SELECT name FROM db2.Fruit;
INSERT INTO Juice ( name ) SELECT name FROM db2.Juice;
CREATE TEMPORARY TABLE Recipe_tmp AS
SELECT Juice.name AS j_name, Fruit.name AS f_name
FROM db2.Recipe, db2.Fruit, db2.Juice
WHERE db2.Recipe.juice_id = db2.Juice.id AND db2.Recipe.fruit_id = db2.Fruit.id
;
INSERT INTO Recipe ( juice_id, fruit_id ) SELECT j.id, f.id
FROM Recipe_tmp AS r, Juice AS j, Fruit AS f
WHERE r.j_name = j.name AND r.f_name = f.name
;
DROP TABLE Recipe_tmp;
COMMIT;
DETACH DATABASE db1;
DETACH DATABASE db2;
602392714
清零编程群