[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2014-08-25
20
Medium Priority
?
240 Views
Last Modified: 2014-09-22
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';
0
Comment
Question by:adrian lujan
  • 11
  • 9
20 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40284549
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
 

Author Comment

by:adrian lujan
ID: 40284670
is a merge statement with a type 2 dimension. Am I doing something wrong here?
0
 

Author Comment

by:adrian lujan
ID: 40284760
The line of business is the target table and I need to keep history of the changes .
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Expert Comment

by:chaau
ID: 40284765
Have you tried to use my select statement as a source?
0
 

Author Comment

by:adrian lujan
ID: 40284782
I get errors
0
 

Author Comment

by:adrian lujan
ID: 40284785
Is just not clear where to implement the code
0
 
LVL 25

Expert Comment

by:chaau
ID: 40284795
Have you tried the select statement on its own? Does it work?
0
 

Author Comment

by:adrian lujan
ID: 40284837
yes I did but I get a lot of red lines not sure where to add it
0
 

Author Comment

by:adrian lujan
ID: 40284841
can u just change my code and replace it with your select statement?
0
 
LVL 25

Expert Comment

by:chaau
ID: 40284866
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
 

Author Comment

by:adrian lujan
ID: 40284872
Msg 156, Level 15, State 1, Line 60
Incorrect syntax near the keyword 'OVER'.

thats what I get
0
 
LVL 25

Expert Comment

by:chaau
ID: 40284878
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
 

Author Comment

by:adrian lujan
ID: 40284891
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
 
LVL 25

Expert Comment

by:chaau
ID: 40284895
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
 

Author Comment

by:adrian lujan
ID: 40284900
ok I will tomorrow since that will be more time consuming. thanks for your help
0
 
LVL 25

Expert Comment

by:chaau
ID: 40284904
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
 

Author Comment

by:adrian lujan
ID: 40284912
thats what need with a type 2 dimension
0
 

Author Comment

by:adrian lujan
ID: 40288070
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
 
LVL 25

Expert Comment

by:chaau
ID: 40288120
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
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40289326
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question