Database Reference
In-Depth Information
■
it may help to have the listing files open in sQL Management studio as we discuss them. All of the sQL
code we are discussing in this topic is provided for you as part of the downloadable book files. The files for this
chapter are found in the
C:\_BookFiles\Chapter06Files
folder.
Note
Listing 6-1.
Deleting Data from the Data Warehouse Tables Using the Delete Command
Delete From dbo.FactSales
Delete From dbo.FactTitlesAuthors
Delete From dbo.DimTitles
Delete From dbo.DimPublishers
Delete From dbo.DimStores
Delete From dbo.DimAuthors
If you choose to use the truncation statement here, your code must include statements that drop the
foreign key relationships before truncation. Listing 6-2 is an example of what your SQL code looks like using the
TRUNCATE
command.
Listing 6-2.
Truncating the Table Data and Resetting the Identity Values
/****** Drop Foreign Key s ******/
Alter Table [dbo].[DimTitles] Drop Constraint [FK_DimTitles_DimPublishers]
Alter Table [dbo].[FactTitlesAuthors] Drop Constraint [FK_FactTitlesAuthors_DimAuthors]
Alter Table [dbo].[FactTitlesAuthors] Drop Constraint [FK_FactTitlesAuthors_DimTitles]
Alter Table [dbo].[FactSales] Drop Constraint [FK_FactSales_DimStores]
Alter Table [dbo].[FactSales] Drop Constraint [FK_FactSales_DimTitles]
Go
/****** Clear all tables and reset their Identity Auto Number ******/
Truncate Table dbo.FactSales
Truncate Table dbo.FactTitlesAuthors
Truncate Table dbo.DimTitles
Truncate Table dbo.DimPublishers
Truncate Table dbo.DimStores
Truncate Table dbo.DimAuthors
Go
/****** Add Foreign Keys ******/
Alter Table [dbo].[DimTitles] With Check Add Constraint [FK_DimTitles_DimPublishers]
Foreign Key ([PublisherKey]) References [dbo].[DimPublishers] ([PublisherKey])
Alter Table [dbo].[FactTitlesAuthors] With Check Add Constraint [FK_FactTitlesAuthors_DimAuthors]
Foreign Key ([AuthorKey]) References [dbo].[DimAuthors] ([AuthorKey])
Alter Table [dbo].[FactTitlesAuthors] With Check Add Constraint [FK_FactTitlesAuthors_DimTitles]
Foreign Key ([TitleKey]) References [dbo].[DimTitles] ([TitleKey])
Alter Table [dbo].[FactSales] With Check Add Constraint [FK_FactSales_DimStores]
Foreign Key ([StoreKey]) References [dbo].[DimStores] ([Storekey])
Alter Table [dbo].[FactSales] With Check Add Constraint [FK_FactSales_DimTitles]
Foreign Key ([TitleKey]) References [dbo].[DimTitles] ([TitleKey])
Go
