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';
adrian lujanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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]?
0
adrian lujanAuthor Commented:
is a merge statement with a type 2 dimension. Am I doing something wrong here?
0
adrian lujanAuthor Commented:
The line of business is the target table and I need to keep history of the changes .
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chaauCommented:
Have you tried to use my select statement as a source?
0
adrian lujanAuthor Commented:
I get errors
0
adrian lujanAuthor Commented:
Is just not clear where to implement the code
0
chaauCommented:
Have you tried the select statement on its own? Does it work?
0
adrian lujanAuthor Commented:
yes I did but I get a lot of red lines not sure where to add it
0
adrian lujanAuthor Commented:
can u just change my code and replace it with your select statement?
0
chaauCommented:
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

0
adrian lujanAuthor Commented:
Msg 156, Level 15, State 1, Line 60
Incorrect syntax near the keyword 'OVER'.

thats what I get
0
chaauCommented:
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

0
adrian lujanAuthor Commented:
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
0
chaauCommented:
It would help if you somehow are able to provide sample data. It would be even helpful if the data is in SQLFiddle.com
0
adrian lujanAuthor Commented:
ok I will tomorrow since that will be more time consuming. thanks for your help
0
chaauCommented:
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
0
adrian lujanAuthor Commented:
thats what need with a type 2 dimension
0
adrian lujanAuthor Commented:
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';
0
chaauCommented:
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

0
chaauCommented:
OK, have a look at this SQL Fiddle. Here is the query:
insert into Customers
     (
     custid,
     companyname,
     phone,
     address,
     isCurrent
     )
     select
    custid,
    companyname,
    phone,
    address,
    'No'    -- this may need to be changed to 'Yes'
     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, iscurrent)
          VALUES (src.custid, src.companyname, src.phone, src.address, 'Yes')
          output $action as [action],deleted.custid, deleted.companyname, deleted.phone, deleted.address) as temp
          where [Action] = 'UPDATE'

Open in new window

that produces this result:
Old Data:
    select * from customers
    | CUSTID | COMPANYNAME |          PHONE |   ADDRESS | ISCURRENT |
    |--------|-------------|----------------|-----------|-----------|
    |      1 |      cust 1 | (111) 111-1111 | address 1 |    (null) |
    |      2 |      cust 2 | (222) 222-2222 | address 2 |    (null) |
    |      3 |      cust 3 | (333) 333-3333 | address 3 |    (null) |
    |      4 |      cust 4 | (444) 444-4444 | address 4 |    (null) |
    |      5 |      cust 5 | (555) 555-5555 | address 5 |    (null) |

    select * from customersStage
    | CUSTID |  COMPANYNAME |          PHONE |   ADDRESS |
    |--------|--------------|----------------|-----------|
    |      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 |

Open in new window

New Data:
    select * from customers
    | CUSTID |  COMPANYNAME |          PHONE |   ADDRESS | ISCURRENT |
    |--------|--------------|----------------|-----------|-----------|
    |      1 |       cust 1 | (111) 111-1111 | address 1 |    (null) |
    |      2 |       cust 2 | (222) 222-2222 | address 2 |        No |
    |      3 |       cust 3 | (333) 333-3333 | address 3 |    (null) |
    |      4 |       cust 4 | (444) 444-4444 | address 4 |    (null) |
    |      5 |       cust 5 | (555) 555-5555 | address 5 |        No |
    |      6 | cust 6 (new) | (666) 666-6666 | address 6 |       Yes |
    |      7 | cust 7 (new) | (777) 777-7777 | address 7 |       Yes |
    |      2 |       cust 2 | (222) 222-2222 | address 2 |        No |
    |      5 |       cust 5 | (555) 555-5555 | address 5 |        No |

Open in new window

Please check if it is all right. It looks right to me.
Please note that when inserting from "deleted" you may need to decide how to populate isCurrent: Yes or No. I think you should just insert the old value from deleted, or make it 'Yes'. At the moment, in the SQL Fiddle it is no: as a result the customer 5 has No for both entries. Please analyse and decide what value to use
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.