SQLite Insert Distinct from 2 variables

I have a SQLite DB with a table with the following kind of data:

CREATE TABLE tableName 
    ([A] int, [B] str, [C] str)
;
    
INSERT INTO tableName 
    ([A], [B], [C])
VALUES
    (1, AAA, AAA),
    (2, AAA, BBB),
    (3, ABC, DEF),
    (4, BBB, AAA),
    (5, CCC, DEF),
    (6, DEF, EEE),
    (7, DEF, DDD),
    (8, DDD, BBB),
    (9, EEE, AAA)
;

What I need to do is take columns B and C, create a single unduplicated list and autoincrement the primary key for the unique values. It needs to check to make sure an entry is not already existing – so the second table would be:

1 - AAA
2 - BBB
3 - ABC
4 - DEF
5 - CCC
6 - EEE
7 - DDD

I think it should be something like this:

con = sqlite3.connect('path//DBv15.db')
cur = con.cursor()
cur.execute("INSERT INTO TableB(E) SELECT DISTINCT A.A A.B FROM TableA A;")

But I’m pretty sure that’s wrong. It needs to scan the table for duplicates prior to adding a new row and I’m getting stuck. I’m using a python program to load and modify my tables.

Answer

Use UNION to extract the values of the 2 columns.

INSERT INTO TableB(E) 
SELECT B FROM TableA
UNION
SELECT C FROM TableA

UNION returns distinct values so there is no need for DISTINCT.

Advertisement

Answer