How to do this action using Merge Condition ???? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to do this action using Merge Condition ????

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-12-09 : 04:48:40

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

52326 Posts

Posted - 2013-12-09 : 06:10:36

[code]
..
UPDATE
SET Target.col1 = Source.col1,
Target.col2 = Source.col2,
...
[/code]
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

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-12-09 : 06:58:33

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 07:25:00

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

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-12-09 : 07:32:12

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 08:42:40

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources