one time load of history table RRS feed

  • Question

  • Suggestion to load a one time history table into a SCD  table

    Do I have to drop the indexes?,

    Tuesday, August 4, 2020 5:25 PM

All replies

  • You can deactivate the index instead of dropping it; after you loaded the data you only have to rebuild the index to "activate" it again.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 5, 2020 7:03 AM
  • ...Also, nothing *forces* you to disable/drop the indexes. But the load is probably faster without the indexes in place.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, August 5, 2020 7:36 AM
  • I deactivated the index and was able to load the data from the history table into the SCD table.

    I got the error below.

    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.

    I taught it was only to be enforced while trying to insert into t the point of recreating the index?

    Friday, August 7, 2020 6:50 PM
  • You can never have duplicates in a unique index.

    If the index is in place and you try to insert duplicates, you get an error.

    If you have duplicates and try to create a unique index, you get an error. 

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, August 7, 2020 7:26 PM
  • Thanks for the response
    Friday, August 7, 2020 8:02 PM