I am trying to import data from a staging table into a production table. Both tables have exact same column names.
I want to update a record when Primary key same, and if any of the column is different.
I want to insert if the key not exists.
So I tried to use Merge statement like below:
MERGE INTO MyschoolHistory T
USING
( SELECT * from XTEMP_SchoolHistory
) S
on (T.YearID=S.YearID and T.SchoolID=S.SchoolID)
WHEN MATCHED THEN UPDATE
Set T.Name=S.Name
,T.Low_Grade=S.Low_Grade
,T.High_Grade=S.High_Grade
,T.Is_Assignment_School=S.Is_Assignment_School
,T.Is_Credit_School=S.Is_Credit_School
,T.Is_Program=S.Is_Program
,T.Is_Paper_School=S.Is_Paper_School
,T.Is_Enrollment_School=S.Is_Enrollment_School
,T.Is_Closed=S.Is_Closed
,T.Update_Date=Sysdate
,T.Update_User='EOY'
WHERE NOT EXISTS
(SELECT S.Yearid, S.Schoolid,S.Name, S.Low_Grade, S.High_Grade, S.Is_Assignment_School,S.Is_Credit_School,S.Is_Program,
S.Is_Paper_School,S.Is_Enrollment_School,S.Is_Closed from S
INTERSECT
SELECT T.YearID, T.SchoolID,T.Name, T.Low_Grade, T.High_Grade, T.Is_Assignment_School,T.Is_Credit_School,T.Is_Program,
T.Is_Paper_School,T.Is_Enrollment_School,T.Is_Closed from T )
WHEN NOT MATCHED THEN INSERT
VALUES (S.Yearid, S.Schoolid,S.Name, S.Low_Grade, S.High_Grade, S.Is_Assignment_School,S.Is_Credit_School,S.Is_Program,
S.Is_Paper_School,S.Is_Enrollment_School,S.Is_Closed,S.Create_Date,S.Create_User, Null, Null)
My question is about the where not exists part, I use a intersect to compare each column, if any of the column is different, then update the entire record.
After run the code, it gives error table does not exist, so I guess it means the S and T not exists. Not sure why
Then I changed to Dual, then it works.
I am surprised it works, because Dual only is supposed to return one row, but it seems many rows are returned from the Intersect clause.
What is the correct syntax?
Thanks much