Im struggling how to migrate datas using .sql script I'm quite new to SQL and trying to figure out how to migrate data's purely on .SQL. I want to add my old data to the new table as a new record with a different structure

Here's my case: I have old two tbl and i want to merge it to my new structured tbl with an additional columns. I'm kinda stuck here since I'm not used in using conditional on .SQL

Prefixes of the tables are schemas

Old table

old.groups

old.sub_groups

New Table: Expected Migrated Data

public.new_groups *id is auto incremented

Fresh New populated table

I want to merge it with conditions. but i can't keep up with SQL queries

Condition 1: If old.groups.id doesn't detect any match on old.sub_groups.parent_id it will be inserted to public.new_groups but the public.new_groups.level and public.new_groups.parent_id will be default to null.

Condition 2: If old.groups.id detects a match on old.sub_groups.parent_id it will be also inserted to public.new_groups then tag the level as 1 (1 means parent group in my structure) but with another new three inserted records which is the sub_groups it detected refer to tbl.new_groups id [3, 4, and 5] and tag the level as 2. and the parent_id will be the parent_id of the old.sub_groups or the id of the parent in old.groups

This is my unfinished Query im only able to call the data its missing out the conditional and the update but i think this is also wrong:

INSERT INTO public.new_groups(
SELECT *, b.sub_group as group_name, b.parent_id FROM old.groups as a 
LEFT JOIN old.sub_groups as b ON a.id = b.parent_id....
)

CodePudding user response：

When you created your table like this:

CREATE TABLE new (
   id SERIAL PRIMARY KEY ,
   group_name VARCHAR(20),
   level INTEGER,
   parent_id INTEGER
);

You can copy the tables with this statement:

INSERT INTO new(group_name, level, parent_id)
SELECT DISTINCT
    group_name,
    CASE WHEN subgroups.parent_id IS NULL THEN NULL ELSE 1 END as level,
    subgroups.parent_id
FROM old
LEFT JOIN subgroups ON old.id = subgroups.parent_id

UNION ALL

SELECT
    sub_group,
    2,
    parent_id
FROM subgroups;

see: DBFIDDLE

just my id starts with 1, and not with 0.