September 7, 2023 at 8:20 am
---Incoming data everytime
Drop table if exists #Temp
Create table #Temp
(Col1 Nvarchar(1000),
Col2 Nvarchar(1000))
Truncate table #Temp
Insert into #Temp
Select '1','AA'
UNION Select '2','AA'
UNION Select '3','AA'
UNION Select '3','AB'
UNION Select '3','AC'
UNION Select '4','AA'
--- this is the Reference table which will not be deleted evertime, it will check if the data has changed or not for its consecutive runs.
Drop table if exists #Temp_Maintable
Create table #Temp_Maintable
(Col1 Nvarchar(1000),
Col2 Nvarchar(1000),
Flag INT)
INSERT INTO #Temp_Maintable
Select *,0 from #Temp
--- Second run data , here 3 AA got changed to 5, AA and it is no more with 3 , so the flag needs to set as 1 for 3 and 5 and new record has added for 10(the flag needs to set as 1)
Truncate table #Temp
Insert into #Temp
Select '1','AA'
UNION Select '2','AA'
UNION Select '5','AA'
UNION Select '3','AB'
UNION Select '3','AC'
UNION Select '4','AA'
UNION Select '10','AA'
/*
This is the expected outcome , hence i have updated , since i knew this is what but for more values and
records it is not the case.
*/
Update #Temp_Maintable Set Flag=1 where Col1=3
Update #Temp_Maintable Set Col1=5, Flag=1 where Col1=3 and Col2='AA'
INSERT INTO #Temp_Maintable
Select *,1 from #Temp where Col1=10
Select * from #Temp_Maintable
How logically try to get this ?
I tried with Left join , but how to get the flag as 1 for 3,5,10.
Here 3 is also set to 1(flag) since AA has moved out now it has only 2 records in the Second time file.
Select *, DENSE_RANK() over( Order by cast(a.col1 as int)) from #Temp as a
LEFT JOIN #Temp_Maintable as b
on a.Col1=b.Col1
and a.Col2 = b.Col2
September 7, 2023 at 8:33 am
I do not understand how your data is ordered – it is neither Col1 nor Col2. Please explain, because without explicit ordering, I do not think that this problem can be solved.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 7, 2023 at 8:46 am
Data is not in any order either Col1 nor Col2, i have taken this as an example in the real time it won't be the case.
Data can change in Col1 & in Col2. Col2 has reference with Col1 , hence if the regroup has been removed or added(Col1=3) then it should also be reloaded for me for different purpose.
As i said i am trying to find the logic, you can ignore my LEFT JOIN logic if is more confusing.
Thanks!
September 7, 2023 at 11:03 am
Any thoughts or suggestions on the above
September 7, 2023 at 11:52 am
Any thoughts or suggestions on the above
As I said, with no ordering, my thoughts are that it's impossible, because the flag could be set against a different row every time.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 7, 2023 at 1:05 pm
Data is not in any order either Col1 nor Col2, Data might change in Col1 & in Col2
September 7, 2023 at 2:15 pm
If I'm understanding then perhaps an INSERT statement and an UPDATE statement. Possibly it could also be done using a MERGE statement however I don't use such syntactic sugar. Also, this assumes (Col1, Col2) is a unique tuple
Drop table if exists #Temp_Maintable;
go
Create table #Temp_Maintable(
Col1 Nvarchar(10),
Col2 Nvarchar(10),
Flag INT default 0);
Insert into #Temp_Maintable(Col1, Col2)
Select '1','AA'
UNION Select '2','AA'
UNION Select '3','AA'
UNION Select '3','AB'
UNION Select '3','AC'
UNION Select '4','AA';
Drop table if exists #Temp;
go
Create table #Temp(
Col1 Nvarchar(10),
Col2 Nvarchar(10));
Insert into #Temp
Select '1','AA'
UNION Select '2','AA'
UNION Select '5','AA'
UNION Select '3','AB'
UNION Select '3','AC'
UNION Select '4','AA'
UNION Select '10','AA';
--select * from #Temp;
/* without unique tuple (Col1, Col2) this doesn't work*/
create unique index ndx_unq_tm on #Temp_Maintable(Col1, Col2);
create unique index ndx_unq_tm on #Temp(Col1, Col2);
/* make sure the (multiple) DML statements which follow are rolled back as a single transaction */
set xact_abort on;
/* insert nonexistant rows */
with only_exits_in_t_cte as (
select t.*
from #Temp t
where not exists (select 1
from #Temp_Maintable tm
where tm.Col1=t.col1
and tm.col2=t.col2))
insert #Temp_Maintable(col1, col2, flag)
select *, 1
from only_exits_in_t_cte;
/* delete existant rows and output old Col1 values */
Drop table if exists #Temp_exists;
go
Create table #Temp_exists(
Col1 Nvarchar(10),
Col2 Nvarchar(10));
with
exits_only_in_tm_cte as (
select tm.Col1, tm.Col2
from #Temp_Maintable tm
where not exists (select 1
from #Temp t
where t.Col1=tm.col1
and t.col2=tm.col2))
delete tm
output deleted.Col1, deleted.Col2 into #Temp_exists
from #Temp_Maintable tm
join exits_only_in_tm_cte otm on tm.Col1=otm.Col1
and tm.Col2=otm.Col2;
if @@rowcount>0
begin
/* make sure the update uses unique values on the righthand side of join*/
declare @unq_col1 table(col1 nvarchar(10) unique not null);
insert @unq_col1 select distinct col1 from #Temp_exists;
/* update based on unique Col1 values */
update tm
set flag=1
from #Temp_Maintable tm
join @unq_col1 uc on tm.Col1=uc.col1;
end
select *
from #Temp_Maintable;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
September 7, 2023 at 3:42 pm
This gives you the expected results. You'll probably need to feed the CTE into a MERGE statement to get the updates that you need.
WITH Temp_Changes AS
(
SELECT a.*, MAX(CASE WHEN b.Col1 IS NULL THEN 1 WHEN a.Col1 IS NULL THEN 1 ELSE 0 END) OVER(PARTITION BY COALESCE(a.Col1, b.Col1)) AS Flag
FROM #Temp AS a
FULL OUTER JOIN #Temp_Maintable AS b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
)
SELECT *
FROM Temp_Changes
WHERE Temp_Changes.Col1 IS NOT NULL;
Also, when giving expected results, DO NOT UPDATE THE SAMPLE DATA. Provide a completely separate table with expected results. I want to be able to run the query multiple times without having to worry about whether the sample data has changed.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 11, 2023 at 3:42 am
Thank you all , yes it worked as expected.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply