One time history load into dimension table

Question

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,

Answer 1

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

Answer 2

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]

Answer 3

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.

Answer 4

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

Answer 5

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

Answer 6

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.

Answer 7

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.

Answer 8

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'

Answer 9

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

Answer 10

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?

Answer 11

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

Answer 12

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.

Answer 13

what is the best way to add a field to an existing SCD dimension and then load history for that field?