Upsert or Merge 2 Tables?
-
I apologize if this belongs in the newb forum, but I'm not sure if this is hard or I'm just thick.
I have two tables (let's call them 'old' and 'new') with the same two columns in each (PartTypeNum, PartNum).
There are 7 possible PartTypeNum's (1-7) and 1500 unique PartNum's. Each PartNum has a PartTypeNum.
There are some PartNum's in 'old' that are not in 'new', and vice versa, and some of the PartTypeNum's in 'old' are wrong.
In the end, I need a table that contains all of the distinct PartNum's, with each having the new.PartTypeNum (where new.PartNum isn't in 'old' or new.PartTypeNum <> old.PartTypeNum).
TIA for any hints.
-
create table Merged ( PartTypeNum, PartNum)
go
insert into Merged ( PartTypeNum, PartNum)
select coalesce (new.PartTypeNum, old.PartTypeNum)
, colalesce (new.PartNum, old.PartNum)
from new
FULL OUTER JOIN old
on old.PartNum = new.PartNum
From SQL Server Books On Line
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
COALESCE - returns the first nonnull expression among its arguments.
SQL = Scarcely Qualifies as a Language
-
Awesome, that worked beautifully. I should've come here before spending 10 hours trying to figure it out myself.
Thanks much!
-
Here is another solution that might use less resources:
select new.PartTypeNum
, new.PartNum
from new
union all
select old.PartTypeNum
, old.PartNum
from old
where not exists
(select 1
from new
where new.PartNum = old.PartNum
)
P.S.
Do not bother to include an "order by" because when the "merged" table has a clustered index, SQL server will automatically perform an order by.
SQL = Scarcely Qualifies as a Language
-
Do not bother to include an "order by" because when the "merged" table has a clustered index, SQL server will automatically perform an order by.
Just a comment on that. If no order by is specified, SQL will return the data as-is after finishing the processing. That will often be the order of the clustered index, unless any form of hash or sort is performed during the query, or an parallel executions.
If order is important, an order by should be applied. If the order by is the same as the clustered index, it's often free. If you don't specify an order by, you cannot guarentee the order of the records.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply