Post reply

How do I truncate a Dimension table with foreign keys to the Fact table?

  • July 11, 2008 at 2:12 am

    #195197

    How do I truncate a Dimension table with foreign keys to the Fact table?

  • July 11, 2008 at 3:25 am

    #841320

    You can either drop the foreign keys, truncate the table and then recreate the foreign keys, or you can use DELETE FROM instead of TRUNCATE TABLE

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 11, 2008 at 8:31 am

    #841542

    Hi,

    the disadvantage of dropping the fk_relationship is that it could last a while until it is recreated. In SQL 2005 there is another way, you could disable the fk, do the deletes, fill the table again and then enable them.

    here is a description how to do this:

    http://www.simple-talk.com/sql/database-administration/foreign-keys-and-their-states/

    It is often discussed if it is even useful to use fk_constraints in a datawarehouse, because your ETL should ensure that the keys are matched. But that is another forum entry.

    Kindest Regards,

    SK

  • July 13, 2008 at 11:54 pm

    #842193

    Thank you so very much, that was very helpful.

  • July 16, 2008 at 5:31 am

    #843690

    USE Database

    DELETE FROM [Scheme].[Table]

    DBCC CHECKIDENT ('[Scheme].[Table]' , RESEED, 0)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply