m7uh3U96

1INSERT INTO new_table (x,y) 
2select A.x,B.y 
3from A 
4  join B on A.w = B.z 
5RETURNING id,B.z;
6	
7with x as (
8select nextval('new_table_id_seq') as new_id, A.x, B.y
9from A JOIN  B ON A.w = B.z
10),
11y as (
12    insert into new_table (id, x,y) select new_id, x,y from x
13)
14select new_id, x,y from x;
15	
16with ins (id, x, y) as
17( insert into new_table (x, y) 
18  select A.x, B.y 
19  from A join B on A.w = B.z 
20  returning id, x, y
21)
22-- insert into another_table (id, z)
23select 
24    ins.id, B.z        -- whatever columns from the 3 tables
25from ins 
26  join A on A.x = ins.x 
27  join B on B.y = ins.y and A.w = B.z ;
28	
29-- The already existing data 
30DROP TABLE IF EXISTS sourceData;
31CREATE TABLE sourceData (
32    -- This table holds the data that is to be migrated to groups, users, emails
33    gid serial PRIMARY KEY,
34    groupName varchar (50) NOT NULL,
35    personName varchar (255) ,
36    emailAddress varchar(255),
37    phone varchar (20) 
38);
39
40INSERT INTO sourceData
41    (groupName, personName, emailAddress, phone)
42    VALUES
43    ('Group A','Philippe','phil@psql.org', NULL),
44    ('Group A','Philippe',NULL,'0123456789'),
45    ('Group A','Francis','francis@gmail.com', NULL),
46    ('Group B','Patrick','patrick@kickmyass.com','0147852369'),
47    ('Group B','Robert',NULL,'0963852741'),
48    ('Group B','Alice', NULL, NULL)
49;
50
51-- 
52
53DROP TABLE IF EXISTS groups CASCADE;
54DROP TABLE IF EXISTS users CASCADE;
55DROP TABLE IF EXISTS emails CASCADE;
56DROP TABLE IF EXISTS phones CASCADE;
57
58CREATE TABLE groups (
59    -- table with names of groups
60    gid serial PRIMARY KEY,
61    group_name varchar(50) NOT NULL
62);
63
64CREATE TABLE users (
65    -- table to join companies with various contact adresses, which may be emails, phone numbers, etc ...
66    -- each person may have several of them
67    gid serial PRIMARY KEY,
68    gid_groups integer NOT NULL,
69    personName varchar(255) NOT NULL,
70    CONSTRAINT fkt2 FOREIGN KEY (gid_groups) REFERENCES groups (gid)
71);
72
73CREATE TABLE emails (
74    -- table with email adresses
75    gid serial PRIMARY KEY,
76    gid_users integer NOT NULL,
77    email_address varchar(255) NOT NULL,
78    CONSTRAINT fkt3 FOREIGN KEY (gid_users) REFERENCES users (gid)
79);
80
81CREATE TABLE phones (
82    -- table with phone numbers
83    gid serial PRIMARY KEY,
84    gid_users integer NOT NULL,
85    phone_number varchar(20) NOT NULL,
86    CONSTRAINT fkt4 FOREIGN KEY (gid_users) REFERENCES users (gid)
87);
88
89-- Now we start with the names already inserted
90INSERT INTO groups (group_name)
91    SELECT DISTINCT groupName FROM sourceData ;
92-- we are not even sure in which order they are inserted
93	
94-- Insertion of users and their email adresses for those who have some
95WITH selectedData  AS 
96    (   -- selection of the data that needs to be inserted
97        SELECT 
98            gp.gid AS gp_gid, sourceD.personName , sourceD.emailAddress
99            FROM groups AS gp
100            JOIN sourceData AS sourceD
101            ON gp.group_name = sourceD.groupName
102            WHERE sourceD.emailAddress IS NOT NULL
103    ),
104insertUsers AS
105    (
106        INSERT INTO users
107            ( gid_groups , personName  )
108            SELECT 
109                gp_gid , personName
110                FROM selectedData
111            RETURNING gid_groups AS gp_gid , gid AS user_gid , personName
112    )
113INSERT INTO emails
114    ( gid_users , email_address )
115    SELECT 
116        insertUsers.user_gid , selectedData.emailAddress
117        FROM insertUsers
118        JOIN selectedData
119        ON insertUsers.personName = selectedData.personName
120;
121
122-- Insertion of phone numbers for users who already exist in the database
123WITH selectedData  AS 
124    (   -- selection of the data that needs to be inserted
125        SELECT 
126            gp.gid AS gp_gid, sourceD.personName , sourceD.phone
127            FROM groups AS gp
128            JOIN sourceData AS sourceD
129            ON gp.group_name = sourceD.groupName
130            WHERE sourceD.phone IS NOT NULL
131    )
132INSERT INTO phones
133    ( gid_users , phone_number  )
134    SELECT 
135        users.gid , selectedData.phone
136        FROM selectedData
137        INNER JOIN users -- ensures that only already existing users are selected
138        ON selectedData.personName = users.personName
139;
140
141-- Insertion of users with their phone numbers for those who have no email address and thus do not exist yet in the database
142WITH selectedData  AS 
143    (   -- selection of the data that needs to be inserted
144        SELECT 
145            gp.gid AS gp_gid, sourceD.personName , sourceD.phone
146            FROM groups AS gp
147            JOIN sourceData AS sourceD
148            ON gp.group_name = sourceD.groupName
149            WHERE sourceD.phone IS NOT NULL
150    ),
151insertUsers AS
152    (
153        INSERT INTO users
154            ( gid_groups , personName  )
155            SELECT 
156                selectedData.gp_gid , selectedData.personName
157                FROM selectedData
158                LEFT JOIN users
159                ON selectedData.personName = users.personName
160                WHERE  users.personName IS NULL -- ensures that only users not already in database are inserted
161            RETURNING gid_groups AS gp_gid , gid AS user_gid , personName
162    )
163INSERT INTO phones
164    ( gid_users , phone_number )
165    SELECT 
166        insertUsers.user_gid , selectedData.phone
167        FROM insertUsers
168        JOIN selectedData
169        ON insertUsers.personName = selectedData.personName
170;