locked
One time history load into dimension table RRS feed

  • Pytanie

  • I have a slowly changing dimension table that keeps track record of our current patient from the patients current location.

    There is also a history table.

    I want to load the history table into the dimension table how do I get past duplicate violation msg

    Msg 2601, Level 14, State 1, Line 13

    Cannot insert duplicate key row in object 'dim.dim_table_bed_SCD2' with unique index 'dim_table_bed_SCD2_idx_A'.

    Do I have to drop the indexes then rebuild it after load to achieve this?

    Thanks,

    wtorek, 4 sierpnia 2020 21:47

Odpowiedzi

  • Hi Dre01,

    Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

    Thank you for understanding!

    Best Regards   
    Echo


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    • Oznaczony jako odpowiedź przez Dre01 wtorek, 18 sierpnia 2020 19:46
    wtorek, 11 sierpnia 2020 01:50

Wszystkie odpowiedzi

  • Can you please script the definition of that index and post it?

    Also, how exactly you're loading the data - why you're creating duplicate rows?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    wtorek, 4 sierpnia 2020 21:52
  • USE [OSH_Repository]
    GO
    
    /****** Object:  Index [dim_table_bed_SCD2_idx_0]    Script Date: 8/4/2020 3:28:11 PM ******/
    ALTER TABLE [dim].[dim_table_bed_SCD2] ADD  CONSTRAINT [dim_table_bed_SCD2_idx_0] PRIMARY KEY CLUSTERED 
    (
    	[dim_table_bed_scd2_key] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    
    CREATE UNIQUE NONCLUSTERED INDEX [dim_table_bed_SCD2_idx_A] ON [dim].[dim_table_bed_SCD2]
    (
    	[patid] ASC,
    	[episode_number] ASC,
    	[dss_current_flag] ASC,
    	[dss_version] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    CREATE UNIQUE NONCLUSTERED INDEX [dim_table_bed_SCD2_idx_SC] ON [dim].[dim_table_bed_SCD2]
    (
    	[patid] ASC,
    	[episode_number] ASC,
    	[bed_identifier] ASC,
    	[room_number] ASC,
    	[unit_value] ASC,
    	[dss_current_flag] ASC,
    	[dss_version] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


    • Zmodyfikowany przez Dre01 wtorek, 4 sierpnia 2020 22:37
    wtorek, 4 sierpnia 2020 22:33
  • I am using merge with not matched.

    Its a duplicate because the primary keys are patid and episode number.

    Since this is a one time load there are different rows with the same patid and episode number.

    wtorek, 4 sierpnia 2020 22:36
  • May be you need to make sure to insert with the higher version (e.g. can these two columns make your new values unique) ?
    [dss_current_flag] ASC,
    	[dss_version] ASC

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    środa, 5 sierpnia 2020 04:42
  • What would you want to insert anyhow those rows? How the table will look like after the one time load?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    środa, 5 sierpnia 2020 05:03
    Autor odpowiedzi
  • Hi Dre01,

    Please refer to below solution:

    (1) Remove the unique index or change to a non-unique index
    (2) The field(created a unique index) is assigned and cannot have duplicate values
    (3)Use the index option (IGNORE_DUP_KEY) to filter to avoid errors


    CREATE TABLE #Test (C1 INT)
    CREATE UNIQUE INDEX AK_Index ON #Test (C1)
        WITH (IGNORE_DUP_KEY = ON)
    INSERT INTO #Test VALUES (1)
    INSERT INTO #Test VALUES (1)	--Duplicate keys ignored
     
    SELECT * FROM #Test
    
    DROP TABLE #Test


    Best Regards 
    Echo 


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.




    • Zmodyfikowany przez Echo Liuz środa, 5 sierpnia 2020 07:35
    środa, 5 sierpnia 2020 07:13
  • I don't want to filter any row out. All rows are required .Its a one time load of the history table

    I want to preserve all the rows. for example.

    The duplicate is as a result of patid and episode are the business keys and since it is a history load .

    I want to flag all the records as 'N' since they are not the current value.

    środa, 5 sierpnia 2020 16:22
  • I don't understand when you said higher version.

    And yes from the error example below.

    Cannot insert duplicate key row in object 'dim.dim_table_bed_SCD2' with unique index 'dim_table_bed_SCD2_idx_A'. The duplicate key value is (1008, 2, N, 1).

    Those 2 fields wont be unique.

    Because I am entering the Same value for all the rows.

    [dss_current_flag] ='N'
    [dss_version] ='1'

    środa, 5 sierpnia 2020 16:29
  • Hi,

    Can you use incremental number for dss_version (e.g. get the maximum number from existing data and then just use maxNumber + row_number() over (partition by PaitId, EpisodeId order by newId()) ? This would help you to make the rows unique (assuming that this column is not significant and you can make it unique)?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    środa, 5 sierpnia 2020 17:07
  • I deactivated the index and was able to load the data from the history table into the SCD table.

    I got the error below when trying to recreate the index.

    Msg 1505, Level 16, State 1, Line 1

    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dim.dim_table_bed_SCD' and the index name 'dim_table_bed_SCD_idx_A'. The duplicate key value is (1849, 1, N, 1).

    The statement has been terminated.

    Is the error as a result that; there lies duplicate result in the loaded table due to diabling the index.

    I taught it was only to be enforced while trying to insert into the table and not at the point of recreating the index?


    • Zmodyfikowany przez Dre01 piątek, 7 sierpnia 2020 19:31
    piątek, 7 sierpnia 2020 18:54
  • No, the records in the table need to be also covered by the index. You cannot recreate that index if there are duplicates in the table. 

    That's why I originally suggested to try to make these rows unique somehow. Now you would need to make them unique before re-creating the index.


    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    piątek, 7 sierpnia 2020 19:42
  • Hi Dre01,

    Please remember to mark the replies as answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

    Thank you for understanding!

    Best Regards   
    Echo


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    • Oznaczony jako odpowiedź przez Dre01 wtorek, 18 sierpnia 2020 19:46
    wtorek, 11 sierpnia 2020 01:50
  • what is the best way to add a field to an existing SCD dimension and then load history for that field?
    piątek, 21 sierpnia 2020 16:04