On 2017/08/11 7:50 PM, Roman Fleysher wrote:
Dear SQLiters,

I have two tables linked by a foreign key, linkID. I need to transfer content 
of these two tables into two corresponding tables in another database 
preserving the link. However, the second database already has records and 
numeric value of linkID can not be preserved. Nor its value is important 
because it simply links the tables. How do I INSERT into two tables, preserving 
the link while allowing for value of linkID to change. LinkID is defined as 
INTEGER PRIMARY KEY.

It looks to me that I should create a temporary view on the both tables in the 
second database and try to use INSTEAD OF trigger. Is that the right way? Or I 
have to create a temp table that maps old linkID to new linkID, somehow.

It's a little hard to follow, but if I understand correct, you have one table with a foreign key parent in another table. You wish to amalgamate these tables with two similar tables in another database while retaining the foreign-Key relationships, BUT, the target tables (to be added to) already has the relationship set up and might have keys (or key-values rather) that may already have been used and so would be duplicated if you simply INSERT all the data - which is obviously not possible or wanted.

Further to this, you do not care about the actual values in these keys, only the relationships.

The question I am specifically missing is: Do you mean for this to happen on a regular basis? Or continuously? Or only once after which you will simply continue life using the second DB's tables?

Another question: How large are these tables? i.e. Will it break your storage medium if we duplicate them into Temp tables first?

Either way, assuming that it is a once-off operation (or at least something only done once in a while), the solution can be had by simply appending after the known last Key in the target table, somewhat like this:


-- SQLite version 3.17.0 [ Release: 2017-02-13 ] on SQLitespeed version 2.0.2.4. -- ================================================================================================

-- The first bit is just some test setup to try and mimic the 4 tables in question.
  -- AP and AC is the Parent and Child tables in DB A, and
  -- BP and BC the same Parent and Child Tables in DB B.
  -- The Relation Key is on "linkID"
CREATE TABLE AP (
  ID INTEGER PRIMARY KEY,
  PVal TEXT
);
CREATE TABLE AC (
  ID INTEGER PRIMARY KEY,
  linkID INT REFERENCES AP(ID),
  CVal TEXT
);
CREATE TABLE BP (
  ID INTEGER PRIMARY KEY,
  PVal TEXT
);
CREATE TABLE BC (
  ID INTEGER PRIMARY KEY,
  linkID INT REFERENCES BP(ID),
  CVal TEXT
);

  -- Some test values to make the relationships easy to follow.
INSERT INTO AP(ID,PVal) VALUES (1, 'Fleysher'), (2,'Smith');
INSERT INTO AC(ID,linkID,CVal) VALUES (1, 1, 'Roman'), (2, 1, 'Sam'), (3, 2, 'Ryan'), (4,2, 'John');
INSERT INTO BP(ID,PVal) VALUES (1, 'Hipp'), (2,'Kennedy');
INSERT INTO BC(ID,linkID,CVal) VALUES (1, 1, 'Richard'), (2, 2, 'Dan'), (3, 2, 'John F.');

  -- Some queries to show how the relations manifest in the 2 separate DBs:
SELECT AC.ID, AC.CVal, AP.PVal
  FROM AC
  JOIN AP ON AP.ID = AC.linkID
;
  --      ID      | CVal  | PVal
  -- ------------ | ----- | --------
  --       1      | Roman | Fleysher
  --       2      | Sam   | Fleysher
  --       3      | Ryan  | Smith
  --       4      | John  | Smith

SELECT BC.ID, BC.CVal, BP.PVal
  FROM BC
  JOIN BP ON BP.ID = BC.linkID
;
  --      ID      | CVal    | PVal
  -- ------------ | ------- | -------
  --       1      | Richard | Hipp
  --       2      | Dan     | Kennedy
  --       3      | John F. | Kennedy


-- Here starts the actual script to solve the combination:

-- I couldn't preserve the value across CTEs, so opted for a TEMP table in stead to
  -- get the base value from which to add the A DB values.
CREATE TEMP TABLE TX AS SELECT MAX(ID) AS vMin FROM BP;

INSERT INTO BP(ID,PVal)
SELECT (AP.ID+TX.vMin), AP.PVal
  FROM AP,TX
;
INSERT INTO BC(linkID,CVal)
SELECT (AC.linkID+TX.vMin), AC.CVal
  FROM AC,TX
;
DROP TABLE TX;

-- That's the end of the combination script.

-- A query to simply show the combined DB to ensure the principle is sound.
SELECT BC.ID, BC.CVal, BP.PVal
  FROM BC
  JOIN BP ON BP.ID = BC.linkID
;
  --      ID      | CVal    | PVal
  -- ------------ | ------- | --------
  --       1      | Richard | Hipp
  --       2      | Dan     | Kennedy
  --       3      | John F. | Kennedy
  --       4      | Roman   | Fleysher
  --       5      | Sam     | Fleysher
  --       6      | Ryan    | Smith
  --       7      | John    | Smith


-- The rest is just cleanup for this test script.
DROP TABLE AC;
DROP TABLE AP;
DROP TABLE BC;
DROP TABLE BP;

-- And proof that no FK relations were harmed in the making of this script:

-- Script Stats: Total Script Execution Time: 0d 00h 00m and 00.040s -- Total Script Query Time: 0d 00h 00m and 00.012s
  --                 Total Database Rows Changed:     17
  --                 Total Virtual-Machine Steps:     1283
  --                 Last executed Item Index:        19
  --                 Last Script Error:
-- ------------------------------------------------------------------------------------------------

  -- 2017-08-11 22:16:17.814  |  [Success]    Script Success.
  -- 2017-08-11 22:16:17.816  |  [Success]    Transaction Rolled back.
-- ------- DB-Engine Logs (Contains logged information from all DB connections during run) ------


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to