I can see that you have edited your sample data, but only the part that I quoted in my last post. From what I can see, there are still issues with your Sample Data. The data in the EAN at the Destination does not match with the data at the Source. I am assuming that its a typo error and going with the values at the source.
You can select the data by joining the tables at the Source ON the Id field in both TB1 and TB2. But, you don't have such a field at the Destination. So....I would say that the design at the Destination is terribly wrong and without making changes to this design it won't be possible to do what you are trying to do.
I would have inserted/updated the data by checking the EAN field in the Destination table. But, as I see....the first row has an EAN value "NULL" and the corresponding row at the Destination has a value which is NOT NULL. Which means that even if we know that the two rows are same but we can't assume that it might be true for all the rest of the data on a larger scale.
So, all you could do here is add an "ID" field to the destination table which would map the existing data at the Destination to the data at the Source. If this is done once then it would be a lot lot easier for you to solve such problems in the future.
Finally, if you think that the NAME field is unique and you want to map the data between the Source and the Destination on the NAME field then you can use the following script:
--Creating Tables
Create table Ex
(ID int,
EAN int,
ASIN BigInt,
category NVarchar(20),
NAME NVarchar(20),
CONDITION NVarchar(20) )
Create table Ex1
(ID int,
PRICE Float)
Create Table Ex2
(KID int,
EAN Int,
category NVarchar(20),
NAME NVarchar(20),
CONDITION NVarchar(20),
PRICE Float )
--Inserting Sample Data
Insert Into Ex
Select 1, null, 20001, 'ps2', 'COD2', 'new'
Union ALL
Select 2, 1002, 20002, 'xbox', 'HOLA', 'new'
Union ALL
Select 3, 1003, 20003, 'xbox', 'Spider Man', 'used'
Insert Into Ex1
Select 1, 50
Union ALL
Select 2, 51
Union ALL
Select 3, 61
Insert Into Ex2
Select 1013, 1001, 'ps2', 'COD2', 'new', 50
Union ALL
Select 1015, 1002, 'xbox', 'HOLA', 'new', 31
--Script for your Requirement
Declare @newKID Int
Select @newKID = MAX(KID) From Ex2
IF Exists(Select Name From Ex Except Select Name From Ex2)
Begin
Insert Into Ex2
Select (@newKID + ROW_NUMBER() Over (Order By ID)) As KID, EAN, category, NAME, CONDITION, PRICE From
(
Select a.Id, a.EAN, a.category, a.NAME, a.CONDITION, b.PRICE
From Ex As a JOIN Ex1 As b ON a.ID = b.ID
) As p
Where NAME IN (Select Name From Ex Except Select Name From Ex2)
End
IF EXISTS(Select Name From Ex Intersect Select Name From Ex2)
Begin
Update Ex2
Set EAN = p.EAN, category = p.category, NAME = p.NAME, CONDITION = p.CONDITION, PRICE = p.PRICE
From
(
Select c.EAN, a.category, a.NAME, a.CONDITION, b.PRICE, c.KID
From Ex As a JOIN Ex1 As b ON a.ID = b.ID
JOIN Ex2 As c On a.NAME = c.NAME
) As p
Where Ex2.EAN = p.EAN
End
You can make the necessary changes in the Script according to your actual data after testing it on the sample data. Here I use Ex and Ex1 as Source tables and Ex2 As the Destination Table.
This would not cause performance issues even while inserting a hud load of data and would work in a matter of seconds......as I have avoide using Cursors for Row by Row insertions.
Hope it helps.