I have a SQL table AccessLog, with following columns and sample data:

| id | firstName | lastName | userEmail   | loginTimestamp           |
| -- | --------- | -------- | ----------- | ------------------------ |
| 1  | John      | Smith    | [email protected] | 2021-01-12T02:16:34.020Z | 
| 2  | ED        | Stone    | [email protected] | 2021-02-12T02:26:34.020Z |  
| 3  | ED        | Stone    | [email protected] | 2021-03-12T02:36:34.020Z |  

I'd like to extract 3 columns (firstName, lastName, userEmail) and move it to separate table Client. Client should look like this then:

| id    | firstName | lastName | userEmail   |
| ----- | --------- | -------- | ----------- |
| 1001  | John      | Smith    | [email protected] | 
| 1002  | ED        | Stone    | [email protected] | 

The point is I need a connection between AccessLog and Client table, which should look this way:

| id | clientId  | loginTimestamp           |
| -- | --------- | ------------------------ |
| 1  | 1001      | 2021-01-12T02:16:34.020Z | 
| 2  | 1002      | 2021-02-12T02:26:34.020Z |  
| 3  | 1002      | 2021-03-12T02:36:34.020Z |

AccessLog should reference to Client table through clientId field.

I know how to copy data from one table to another:

INSERT INTO Client (firstName, lastName, userEmail) 
SELECT DISTINCT firstName, lastName, userEmail FROM AccessLog

but how to make a connection between both tables without loosing any data? Can anybody help?

CodePudding user response：

For this you can use a AFTER INSERT TRIGGER so that you can get the new and old ids.

This works without more code as you have only distinct values in your new client list.

CREATE TABLE AccessLog
    (`id` varchar(2), `firstName` varchar(9), `lastName` varchar(8), `userEmail` varchar(11), `loginTimestamp` varchar(24))
;

INSERT INTO AccessLog
    (`id`, `firstName`, `lastName`, `userEmail`, `loginTimestamp`)
VALUES
    ('1', 'John', 'Smith', '[email protected]', '2021-01-12T02:16:34.020Z'),
    ('2', 'ED', 'Stone', '[email protected]', '2021-02-12T02:26:34.020Z'),
    ('3', 'ED', 'Stone', '[email protected]', '2021-03-12T02:36:34.020Z')

CREATE TABLE Client
    (`id` BIGINT AUTO_INCREMENT PRIMARY KEY, `firstName` varchar(9), `lastName` varchar(8), `userEmail` varchar(11))
;

 CREATE Table bridgetable (id BIGINT , clientId  BIGINT , loginTimestamp varchar(24))

CREATE TRIGGER after_client_insert
AFTER INSERT
ON Client FOR EACH ROW
BEGIN
    
        INSERT INTO bridgetable(id, clientId,loginTimestamp)
        SELECT 
           `id`, new.id,loginTimestamp 
        FROM AccessLog WHERE `firstName` = NEW.`firstName` AND `lastName` = NEW.`lastName`
        AND `userEmail` = NEW.`userEmail`;

END

INSERT INTO Client (firstName, lastName, userEmail) 
SELECT DISTINCT firstName, lastName, userEmail FROM AccessLog

SELECT * FROM Client

id | firstName | lastName | userEmail  
-: | :-------- | :------- | :----------
 1 | John      | Smith    | [email protected]
 2 | ED        | Stone    | [email protected]

SELECT * FROM bridgetable

id | clientId | loginTimestamp          
-: | -------: | :-----------------------
 1 |        1 | 2021-01-12T02:16:34.020Z
 2 |        2 | 2021-02-12T02:26:34.020Z
 3 |        2 | 2021-03-12T02:36:34.020Z

db<>fiddle here