SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to do this action using Merge Condition ????
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 12/09/2013 :  04:48:40  Show Profile  Reply with Quote
I have a source table and Destination table
initailly i have loaded some data into destination table through some conditions
Target.col1			= Source.col1
AND Target.col2			= Source.col2

MERGE INTO dbo.TestDestination AS Target
USING (	select col1,
col2,
col3,
col4
  from testsource ) AS Source
			ON		Target.col1			= Source.col1
					AND Target.col2			= Source.col2
				
		WHEN MATCHED 
		THEN 
		UPDATE
			SET Target.col1	= Source.col1,
				Target.col2			= Source.col2,
				Target.col3			= Source.col3,
				Target.col4			= Source.col4
				
				
		WHEN NOT MATCHED 
		THEN 
		INSERT  
			(   [col1]
			   ,[col2]
			   ,[col3]
			   ,[col4]
)
           VALUES
           (Source.[col1]
           ,Source.[col2]
           ,Source.[col3]
           ,Source.[col4]);



but after that i added some more conditions like
Target.col1			= Source.col1
AND Target.col2			= Source.col2
AND Target.col3			= Source.col3
AND Target.col4			= Source.col4

then data will get filtered. So initial data is wrong data as per conditions so i need to delete initial data and load latest filtered data with these conditions.

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/09/2013 :  06:10:36  Show Profile  Reply with Quote

..
UPDATE
			SET Target.col1	= Source.col1,
				Target.col2			= Source.col2,
...

Why do you want to update the contents of col1 and col2 again? you're using same columns to find matching records from tables so i dont think you need to update them.

Now for your delete you need to find the cases where

Target.col1 = Source.col1
AND Target.col2 = Source.col2
and Target.col3 <> Source.col3
AND Target.col4 <> Source.col4
and delete them from dbo.TestDestination
then do the Merge again with new condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 12/09/2013 :  06:58:33  Show Profile  Reply with Quote
i will give you clear picture initially i loaded data from source to destination on these conditions Target.col1 = Source.col1
AND Target.col2 = Source.col2
and after that i again loaded data from source to destination again adding 2 more conditions Target.col1 = Source.col1
AND Target.col2 = Source.col2
AND Target.col3 = Source.col3
AND Target.col4 = Source.col4 so data will be filtered. So initial data loaded is wrong according to these conditions . I need to delete that data and load fresh data on these given conditions

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/09/2013 :  07:25:00  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

i will give you clear picture initially i loaded data from source to destination on these conditions Target.col1 = Source.col1
AND Target.col2 = Source.col2
and after that i again loaded data from source to destination again adding 2 more conditions Target.col1 = Source.col1
AND Target.col2 = Source.col2
AND Target.col3 = Source.col3
AND Target.col4 = Source.col4 so data will be filtered. So initial data loaded is wrong according to these conditions . I need to delete that data and load fresh data on these given conditions

P.V.P.MOhan


best thing would be to truncate and do load again using correct conditions now that you've completed multiple batch loads using different logic.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 12/09/2013 :  07:32:12  Show Profile  Reply with Quote
i have written this query like this ....can you suggest me the best way becuase conditions will change more often

MERGE INTO dbo.TestDes AS Target
USING ( select DrID,
MetricID,
InsuranceGroupId,
DateKey,
PatientID,
ClaimAmt from test ) AS Source
ON Target.PopulationKey = Source.DrID
AND Target.DateKey = Source.DateKey
AND Target.MetricKey = Source.MetricID
AND Target.ClaimAmt = Source.ClaimAmt
AND Target.PatientKey = Source.PatientID
AND Target.HealthPlanKey = Source.InsuranceGroupId

WHEN MATCHED
THEN
UPDATE
SET Target.PopulationKey = Source.DrID,
Target.MetricKey = Source.MetricID,
Target.HealthPlanKey = Source.InsuranceGroupId,
Target.DateKey = Source.DateKey,
Target.PatientKey = Source.PatientID,
Target.ClaimAmt = Source.ClaimAmt
WHEN NOT MATCHED
THEN
INSERT
( [PopulationKey]
,[MetricKey]
,[HealthPlanKey]
,[DateKey]
,[PatientKey]
,[ClaimAmt])
VALUES
(Source.[DrID]
,Source.[MetricID]
,Source.[InsuranceGroupId]
,Source.[DateKey]
,Source.[PatientID]
,Source.[ClaimAmt])

WHEN NOT MATCHED BY SOURCE
AND EXISTS (select 1 from Test d
where Target.PopulationKey = d.DrID
AND Target.DateKey = d.DateKey
AND Target.MetricKey = d.MetricID
AND Target.ClaimAmt = d.ClaimAmt
AND Target.PatientKey = d.PatientID
AND Target.HealthPlanKey = d.InsuranceGroupId)
THEN
DELETE;

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/09/2013 :  08:42:40  Show Profile  Reply with Quote
what does that mean? if your conditions will change more often then you need to also change query accordingly
Can you help us understand why your conditiond change dynamically?


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000