asked on

I coded a merge statement with type 2 dimension and I get msg 8672, whats the best way to fix this?

I know I have duplicates but I really need help with fixing the code since I'm new at SQL, some example would really help. Here's my code so far;

insert into [InsurerAnalyticsPolicyDimension].[LineOfBusiness]
( --Table and columns in which to insert the data
      PolicyNumber,
      QuoteNumber,
      LOB_URN,
      LOBGroupCode,
      LOBGroupName,
      LOBCode,
      LOBName,
      Deleted_Ind,
      Deleted_Ind_Desc,
      SourceTransactionURN
)
-- Select the rows/columns to insert that are output from this merge statement
-- In this example, the rows to be inserted are the rows that have changed (UPDATE).
select
            PolicyNumber,
            QuoteNumber,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            SourceTransactionURN
from
(
-- This is the beginning of the merge statement.
-- The target must be defined, in this example it is our slowly changing
-- dimension table
MERGE into [InsurerAnalyticsPolicyDimension].[LineOfBusiness] as target
-- The source must be defined with the USING clause
USING
(
-- The source is made up of the attribute columns from the staging table.
SELECT
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN

from [InsurerAnalyticsPolicyStaging].[GL&ExcessLOBStagingTable]
) as source
(
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN
) ON --We are matching on the SourceSystemID in the target table and the source table.
(
target.PolicyNumber = source.Policy_Number
)
-- If the ID's match but the CheckSums are different, then the record has changed;
-- therefore, update the existing record in the target, end dating the record
-- and set the CurrentRecord flag to N
WHEN MATCHED
and target.LOB_URN <> source.LOB_URN
and target.IsCurrent='Yes'
THEN
UPDATE SET
IsCurrent='No'
-- If the ID's do not match, then the record is new;
-- therefore, insert the new record into the target using the values from the source.
WHEN NOT MATCHED THEN
INSERT
(
            PolicyNumber,
            QuoteNumber,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            SourceTransactionURN
)
VALUES
(
source.Policy_Number,
source.Policy_Quote_Number,
source.LOB_URN,
source.LOBGroupCode,
source.LOBGroupName,
      source.LOBCode,
      source.LOBName,
      source.Deleted_Ind,
      source.Deleted_Ind_Desc,
      source.Transaction_URN
)
OUTPUT $action,
source.Policy_Number,
source.Policy_Quote_Number,
source.LOB_URN,
source.LOBGroupCode,
source.LOBGroupName,
      source.LOBCode,
      source.LOBName,
      source.Deleted_Ind,
      source.Deleted_Ind_Desc,
      source.Transaction_URN
) -- the end of the merge statement
--The changes output below are the records that have changed and will need
--to be inserted into the slowly changing dimension.
as changes
(
action,
      PolicyNumber,
      QuoteNumber,
      LOB_URN,
      LOBGroupCode,
      LOBGroupName,
      LOBCode,
      LOBName,
      Deleted_Ind,
      Deleted_Ind_Desc,
      SourceTransactionURN
)
where action='UPDATE';

chaau

It looks like there are duplicates by Policy_Number in your source query:

 SELECT
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN

    from [InsurerAnalyticsPolicyStaging].[GL&ExcessLOBStagingTable]

Open in new window

You will need to select one that is more appropriate. This query as a source will give you the latest record by Policy_Quote_Number. Use it as a source and see if it helps

 SELECT
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN
    from (
SELECT
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN,
            ROW_NUMBER() OVER (PARTITION OVER Policy_Number ORDER BY Policy_Quote_Number DESC) rn
    from 
[InsurerAnalyticsPolicyStaging].[GL&ExcessLOBStagingTable]) as s WHERE rn = 1

Open in new window

BTW, why are you inserting the UPDATED records from the MERGE statement back to the same table [LineOfBusiness]?

adrian lujan

ASKER

is a merge statement with a type 2 dimension. Am I doing something wrong here?

adrian lujan

ASKER

The line of business is the target table and I need to keep history of the changes .

chaau

Have you tried to use my select statement as a source?

adrian lujan

ASKER

I get errors

adrian lujan

ASKER

Is just not clear where to implement the code

chaau

Have you tried the select statement on its own? Does it work?

adrian lujan

ASKER

yes I did but I get a lot of red lines not sure where to add it

adrian lujan

ASKER

can u just change my code and replace it with your select statement?

chaau

Your code is huge; did not want to clutter the answer. Anyway, here is the whole SQL statement:

insert into [InsurerAnalyticsPolicyDimension].[LineOfBusiness]
( --Table and columns in which to insert the data
      PolicyNumber,
      QuoteNumber,
      LOB_URN,
      LOBGroupCode,
      LOBGroupName,
      LOBCode,
      LOBName,
      Deleted_Ind,
      Deleted_Ind_Desc,
      SourceTransactionURN
)
-- Select the rows/columns to insert that are output from this merge statement
-- In this example, the rows to be inserted are the rows that have changed (UPDATE).
select    
            PolicyNumber,
            QuoteNumber,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            SourceTransactionURN
from
(
  -- This is the beginning of the merge statement.
  -- The target must be defined, in this example it is our slowly changing
  -- dimension table
  MERGE into [InsurerAnalyticsPolicyDimension].[LineOfBusiness] as target
  -- The source must be defined with the USING clause
  USING
  (
    -- The source is made up of the attribute columns from the staging table.
SELECT
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN
    from (
SELECT
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN,
            ROW_NUMBER() OVER (PARTITION OVER Policy_Number ORDER BY Policy_Quote_Number DESC) rn
    from 
[InsurerAnalyticsPolicyStaging].[GL&ExcessLOBStagingTable]) as s WHERE rn = 1) as source
  (
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN
  ) ON --We are matching on the SourceSystemID in the target table and the source table.
  (
    target.PolicyNumber = source.Policy_Number
  )
  -- If the ID's match but the CheckSums are different, then the record has changed;
  -- therefore, update the existing record in the target, end dating the record
  -- and set the CurrentRecord flag to N
  WHEN MATCHED
  and target.LOB_URN <> source.LOB_URN
  and target.IsCurrent='Yes'
  THEN
  UPDATE SET
    IsCurrent='No'
     -- If the ID's do not match, then the record is new;
  -- therefore, insert the new record into the target using the values from the source.
  WHEN NOT MATCHED THEN  
  INSERT
  (
            PolicyNumber,
            QuoteNumber,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            SourceTransactionURN
  )
  VALUES
  (
    source.Policy_Number,
    source.Policy_Quote_Number,
    source.LOB_URN,
    source.LOBGroupCode,
    source.LOBGroupName,
      source.LOBCode,
      source.LOBName,
      source.Deleted_Ind,
      source.Deleted_Ind_Desc,
      source.Transaction_URN
  )
  OUTPUT $action,
     source.Policy_Number,
    source.Policy_Quote_Number,
    source.LOB_URN,
    source.LOBGroupCode,
    source.LOBGroupName,
      source.LOBCode,
      source.LOBName,
      source.Deleted_Ind,
      source.Deleted_Ind_Desc,
      source.Transaction_URN
    ) -- the end of the merge statement
--The changes output below are the records that have changed and will need
--to be inserted into the slowly changing dimension.
as changes
(
  action,
      PolicyNumber,
      QuoteNumber,
      LOB_URN,
      LOBGroupCode,
      LOBGroupName,
      LOBCode,
      LOBName,
      Deleted_Ind,
      Deleted_Ind_Desc,
      SourceTransactionURN
)
where action='UPDATE';

Open in new window

adrian lujan

ASKER

Msg 156, Level 15, State 1, Line 60
Incorrect syntax near the keyword 'OVER'.

thats what I get

chaau

Sorry, typo:

insert into [InsurerAnalyticsPolicyDimension].[LineOfBusiness]
( --Table and columns in which to insert the data
      PolicyNumber,
      QuoteNumber,
      LOB_URN,
      LOBGroupCode,
      LOBGroupName,
      LOBCode,
      LOBName,
      Deleted_Ind,
      Deleted_Ind_Desc,
      SourceTransactionURN
)
-- Select the rows/columns to insert that are output from this merge statement
-- In this example, the rows to be inserted are the rows that have changed (UPDATE).
select    
            PolicyNumber,
            QuoteNumber,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            SourceTransactionURN
from
(
  -- This is the beginning of the merge statement.
  -- The target must be defined, in this example it is our slowly changing
  -- dimension table
  MERGE into [InsurerAnalyticsPolicyDimension].[LineOfBusiness] as target
  -- The source must be defined with the USING clause
  USING
  (
    -- The source is made up of the attribute columns from the staging table.
SELECT
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN
    from (
SELECT
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN,
            ROW_NUMBER() OVER (PARTITION BY Policy_Number ORDER BY Policy_Quote_Number DESC) rn
    from 
[InsurerAnalyticsPolicyStaging].[GL&ExcessLOBStagingTable]) as s WHERE rn = 1) as source
  (
            Policy_Number,
            Policy_Quote_Number,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            Transaction_URN
  ) ON --We are matching on the SourceSystemID in the target table and the source table.
  (
    target.PolicyNumber = source.Policy_Number
  )
  -- If the ID's match but the CheckSums are different, then the record has changed;
  -- therefore, update the existing record in the target, end dating the record
  -- and set the CurrentRecord flag to N
  WHEN MATCHED
  and target.LOB_URN <> source.LOB_URN
  and target.IsCurrent='Yes'
  THEN
  UPDATE SET
    IsCurrent='No'
     -- If the ID's do not match, then the record is new;
  -- therefore, insert the new record into the target using the values from the source.
  WHEN NOT MATCHED THEN  
  INSERT
  (
            PolicyNumber,
            QuoteNumber,
            LOB_URN,
            LOBGroupCode,
            LOBGroupName,
            LOBCode,
            LOBName,
            Deleted_Ind,
            Deleted_Ind_Desc,
            SourceTransactionURN
  )
  VALUES
  (
    source.Policy_Number,
    source.Policy_Quote_Number,
    source.LOB_URN,
    source.LOBGroupCode,
    source.LOBGroupName,
      source.LOBCode,
      source.LOBName,
      source.Deleted_Ind,
      source.Deleted_Ind_Desc,
      source.Transaction_URN
  )
  OUTPUT $action,
     source.Policy_Number,
    source.Policy_Quote_Number,
    source.LOB_URN,
    source.LOBGroupCode,
    source.LOBGroupName,
      source.LOBCode,
      source.LOBName,
      source.Deleted_Ind,
      source.Deleted_Ind_Desc,
      source.Transaction_URN
    ) -- the end of the merge statement
--The changes output below are the records that have changed and will need
--to be inserted into the slowly changing dimension.
as changes
(
  action,
      PolicyNumber,
      QuoteNumber,
      LOB_URN,
      LOBGroupCode,
      LOBGroupName,
      LOBCode,
      LOBName,
      Deleted_Ind,
      Deleted_Ind_Desc,
      SourceTransactionURN
)
where action='UPDATE';

Open in new window

adrian lujan

ASKER

that didn't work it does not update the new record and when truncating the target table and I run it again I only see LOBCode '00' which is the first one. Do you have an example of a merge statement with a type 2

chaau

It would help if you somehow are able to provide sample data. It would be even helpful if the data is in SQLFiddle.com

adrian lujan

ASKER

ok I will tomorrow since that will be more time consuming. thanks for your help

chaau

BTW, it may help you. I have answered once to a person seeking the similar solution with MERGE and OUTPUT. have a look at this answer

adrian lujan

ASKER

thats what need with a type 2 dimension

adrian lujan

ASKER

I need to update changed records from source table and add the new records to the target table, while keeping a history of the old records. Heres some sample data code so easier for you. I'm able to add the new records and update the iscurrent column to 'no' but instead of keeping the old records it simply makes an extra copy of the new row and I need it to keep a copy of the old row.

CREATE TABLE dbo.Customers
(
custid INT NOT NULL,
companyname VARCHAR(25) NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
iscurrent varchar(10) null
);
go
INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1'),
(2, 'cust 2', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(4, 'cust 4', '(444) 444-4444', 'address 4'),
(5, 'cust 5', '(555) 555-5555', 'address 5');

CREATE TABLE dbo.CustomersStage
(
custid INT NOT NULL,
companyname VARCHAR(25) NOT NULL,
phone VARCHAR(20) NOT NULL,
address VARCHAR(50) NOT NULL,
);
go

INSERT INTO dbo.CustomersStage(custid, companyname, phone, address)
VALUES
(2, 'AAAAA', '(222) 222-2222', 'address 2'),
(3, 'cust 3', '(333) 333-3333', 'address 3'),
(5, 'BBBBB', 'CCCCC', 'DDDDD'),
(6, 'cust 6 (new)', '(666) 666-6666', 'address 6'),
(7, 'cust 7 (new)', '(777) 777-7777', 'address 7');

insert into Customers
(
custid,
companyname,
phone,
address,
isCurrent
)
select
custid,
companyname,
phone,
address
from (
MERGE dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
       ON TGT.custid = SRC.custid
WHEN MATCHED AND
      ( TGT.companyname <> SRC.companyname
       OR TGT.phone <> SRC.phone
       OR TGT.address <> SRC.address) THEN
UPDATE SET
      TGT.IsCurrent = 'No'
      --TGT.companyname = SRC.companyname,
      --TGT.phone = SRC.phone,
      --TGT.address = SRC.address
WHEN NOT MATCHED THEN
      INSERT (custid,companyname, phone, address)
      VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address,'yes')
      output $action as [action],SRC.custid, SRC.companyname, SRC.phone, SRC.address) as temp
      where [Action] = 'UPDATE';

chaau

You need to "OUTPUT" deleted values. These are the values that were replaced by the UPDATE operation. If you analyse the answer that I have posted to the other question you will see that I have used deleted alias.

output $action as [action],deleted.custid, deleted.companyname, deleted.phone, deleted.address) as temp

Open in new window

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial