How to Union Two Tables and Overwrite Preexisting Rows-CodePudding

I want to combine two tables into one, but if in table 1 the account code "Acc1" is the same as in table 2 the account code "Acc1" then I want to take the amount in table 1 as the result

Table 1
Account Code AccountName Project Type Desc Period Amount

Bcc1 AccountBone AA Good PC2000 11/30/2022 700

Acc1 AccountOne AA Good PC2000 12/1/2022 300

Table 2
Account Code AccountName Project Type Desc Period Amount

Acc1 AccountOne AA Good PC2000 12/1/2022 220

Acc2 AccountOne AA Good PC2000 12/2/2022 432

Result
Account Code AccountName Project Type Desc Period Amount

Bcc1 AccountBone AA Good PC2000 11/30/2022 700

Acc1 AccountOne AA Good PC2000 12/1/2022 300

Acc2 AccountOne AA Good PC2000 12/2/2022 432

I was expecting a query for this case

CodePudding user response：

One way would be a full outer join and an unpivoting technique to select the columns from T1 if there was a match or T2 otherwise (DB Fiddle)

SELECT CA.*
FROM   Table1 t1
       FULL JOIN Table2 t2
              ON t1.AccountCode = t2.AccountCode
       CROSS APPLY (SELECT t1.AccountCode,
                           t1.AccountName /*and other columns*/
                    WHERE  t1.AccountCode IS NOT NULL
                    UNION ALL
                    SELECT t2.AccountCode,
                           t2.AccountName /*and other columns*/
                    WHERE  t1.AccountCode IS NULL) CA

CodePudding user response：

The question is a bit confusing. However, based on your results wouldn't this just work:

DROP TABLE IF EXISTS #TempTable1;
DROP TABLE IF EXISTS #TempTable2;

CREATE TABLE #TempTable1
(
    AccountCode VARCHAR(100),
    AccountName VARCHAR(100)
);

CREATE TABLE #TempTable2
(
    AccountCode VARCHAR(100),
    AccountName VARCHAR(100)
);

GO

INSERT INTO #TempTable1(AccountCode, AccountName)
VALUES('Bcc1', 'AccountBone'),
('Acc1', 'AccountOne');

INSERT INTO #TempTable2(AccountCode, AccountName)
VALUES('Acc1', 'AccountOne'),
('Acc2', 'AccountOne');

SELECT *
FROM #TempTable1
UNION
SELECT *
FROM #TempTable2;

CodePudding user response：

You could select all from Table1 and from Table2 select all that do not exist in Table1:

select * 
from Table1
union
select * 
from Table2
where AccountCode not in (
  select AccountCode
  from Table1
)

See a dbfiddle