CODEWITHSUNDEEP
sqlsql-server-2012
user3788671
user3788671

Reputation: 2057

Compare Two SQL Tables (Exact Same Columns) Add Missing Rows

I have two tables that are in the exact same format. I want to compare both of the these tables to each other and if table A does not have a record that table B has then I want to add that column to Table A. How can I achieve this using SQL. I only want to compare the tables on the following columns CompanyNumber, EmployeeNumber,StatusFlag, and Initials.

What is the best way to do this using SQL?

What Is Wrong with this query?

MERGE EmployeeMaster AS Dest
USING ( SELECT * FROM EmployeeMasterCopy ) AS Src
JOIN  Dest.CompanyNumber = Src.CompanyNumber
and Dest.EmployeeNumber = Src.EmployeeNumber
and Dest.StatusFlag = Src.StatusFlag
and Dest.Initials = Src.Initials
WHEN NOT MATCHED THEN

   INSERT INTO EmployeeMaster(CompanyNumber, FirstName, LastName, FullName, Branch, DepartmentNumber, Initials, 
                                    JobTitle, StartDate, EmployeeNumber, EmployeeType,
                                    PayType, Rate, UnionNo, G2ID, EnterTimeFl, StatusFlag, CreateBy, CreateDate, MiddleName,ManagerEmpNo, NextReviewDate, UserName, TempFl, PrimaryDivision,PEWFl,PGTFl,PMPFl,PPGEFl,PPGFl,PRCFl,PTCFl,PPFl,SWPFl,ReviewPeriod, CorpFl, NickName, RateEffectiveDate, VacationBalance, SickBalance, TempEmployeeType, BusinessSegment)
        VALUES (Src.CompanyNumber, Src.FirstName, Src.LastName,Src.LastName + ', ' + Src.FirstName, Src.Branch, Src.DeptNo, upper(Src.Initials), Src.JobTitle, Src.StartDate, Src.EmployeeNumber,
                Src.EmployeeType, Src.PayType, Src.Rate, Src.UnionNo, Src.G2ID, Src.EnterTimeFl, 1, 'AJOHNSON', GETDATE(), Src.MiddleName, Src.ManagerEmpNo, Src.NextReviewDate, Src.UserName, Src.TempFl, Src.PrimaryDivision,Src.PEWFl,Src.PGTFl,Src.PMPFl,Src.PPGEFl,Src.PPGFl,Src.PRCFl,Src.PTCFl,Src.PPFl,Src.SWPFl,Src.ReviewPeriod, Src).CorpFl, Src.NickName,Src.RateEffectiveDate ,
    Src.VacationBalance ,
    Src.SickBalance ,
    Src.TempEmployeeType, Src.BusinessSegment

Here are the errors:

 Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 128, Level 15, State 1, Line 13
The name "Src.CompanyNumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Upvotes: 0

Views: 304

Answers (2)

radar
radar

Reputation: 13425

you can use MERGE 

MERGE TableA as Dest
USING ( select * from TableB ) as Src
ON Dest.CompanyNumber = Source.companyNumber
and Dest.EmployeeNumber = Source.EMployeeNumber
and Dest.StatusFlag = Source.StatusFlag
and Dest.initials = Source.initials
WHEN NOT MATCHED then
   INSERT (CompanyNumber, EmployeeNumber, StatusFlag, initials, ...)
  values ( Src.CompanyNumber, Src.EmployeeNumber, Src.StatusFlag, Src.initials....)

Upvotes: 2

user4227507
user4227507

Reputation:

Think a simple way could be:

select
   TableA.CompanyNumber as "Tab A Company No",
   TableB.CompanyNumber as "Tab B Company No",
   TableA.EmployeeNumber as "Tab A Employee No",
   TableB.EmployeeNumber as "Tab B Employee No",
   TableA.StatusFlag as "Tab A Status Flag",
   TableB.StatusFlag as "Tab B Status Flag",
   TableA.Initials as "Tab A Initials",
   TableB.Initials as "Tab B Initials",
from
   TableA,
   TableB

Upvotes: 0

Related Questions