How to Make Critera for Data migration

Question

Goal:
Load the destination table c with data from table a and b.

Problem:
I have two tables, a and b with that need to be migrated into same table c.

Need to load one table at a time. When loaded to table c, the criteria för table a need to define that criteria most be access 1 and for the table b the access most be 2.

The question is:
How do you make criteria for the for the table c?

Requested result for table c:

table c
----------
number access gender
--------------------
1      1      2
1      2      2
2      2      2
3      1      1
4      2      1
5      1      2
5      2      2

---

table a
----------
number access gender
--------------------
1      1      2
2      1      2
3      1      1
4      1      1
5      1      2


table b
----------
number access gender
--------------------
1      2      2
2      2      2
3      2      1
4      2      1
5      2      2


table c
----------
number access gender
--------------------
1      1      null
1      2      null
2      2      null
3      1      null
4      2      null
5      1      null
5      2      null

Answer 1

I think I understand what you are trying to do, try using a couple of MERGE statements like so:

MERGE c AS target
USING a AS source 
ON (target.number = source.number, target.access = source.access) 
WHEN MATCHED THEN 
UPDATE SET target.gender = source.gender
WHEN NOT MATCHED BY target THEN 
INSERT (number, access, gender) 
VALUES (source.number, source.access, source.gender)

MERGE c AS target
USING b AS source 
ON (target.number = source.number, target.access = source.access) 
WHEN MATCHED THEN 
UPDATE SET target.gender = source.gender
WHEN NOT MATCHED BY target THEN 
INSERT (number, access, gender) 
VALUES (source.number, source.access, source.gender)