[SQL Server Data Warehousing] How to avoid primary key violation and insert rows based on following scenario??

Hi All,

Appreciate all your advice and comments on below issue pls,

Thanks .

Mira.

what I am going to achieve here is to combine rows in two tables , based on policy_id and monthkey and get SUM of columns of netpaid, GST , AND a specific value from another table base on value on ‘TYPE’ column.

Then I need to  insert values into a factTable, which has columns as below:

Policy_id (pk), monthkey(pk), netPaid(decimal), GST(decimal), claim_amount(decimal)

I am using a stored procedure to generate two temp tables as below, which then combine using a UNION clause before Merge statement use to insert data into factTable. 

Temp table names are   (#Summary_Cancel   , #Summary_Active   )  produces following outputs ..

#Summary_Cancel

Policy_id	Monthkey	netPaid	GST	Type
150	201303	415.00	41.50	99
151	201303	350.00	35.00	99
174	201304	110.00	11.00	99

#Summary_Active

Policy_id	Monthley	netPaid	GST	Type
150	201303	2500.00	250.00	1
151	201301	3000.00	300.00	1
174	201304	5500.00	550.00	1

Then I am using UNION statement as follows:

Select policy_id, Monthkey, netPaid, GST, Type

Into # FinalSummary

From #Summary_Cancel

UNION ALL

Select policy_id, Monthkey, netPaid, GST, Type

From #Summary_Active

Then I use Merge Statement  Target as factTable, to insert values , in here I use value of  ‘Type’ column in the tables  to select a value from another table (cm) using a CASE statement,

 

something like  CASEWHEN FinalSummary.Type = 99 THEN 0

                  ELSE cm.outstanding_amnt

             END AS outstanding_amnt

 

I expect to see final output in factTable as below:, i.e. what I want to group rows on policy_id and Monthkey and do SUM function to add relative columns and insert values into fact Table, which is not happening now with UNION ALL clause, and when I am getting "Violation of PRIMARY KEY constraint" error message.

Main issue is "Type" column which does not need to 'SUM' its merely used as to find

value from other Table ( Using CASE )

Policy_id	Monthkey	netPaid	GST	Type
150	201303	2915.00	291.50	It will get if from cm
151	201303	350.00	35.00	It will get if from cm
151	201301	3000.00	300.00	It will get if from cm
174	201304	5610.00	561.00	It will get if from cm

.

social.technet.microsoft.com/Forums