If I want to Drop my table and recreate it, how would I alter this SQL code? Ste

Question

If I want to Drop my table and recreate it, how would I alter this SQL code?
Step 1: Drop foreign keys

ALTER TABLE dbo.DimLocation DROP CONSTRAINT [FK_DimLocation_DimCity]
ALTER TABLE dbo.FactTrips DROP CONSTRAINT [FK_FactTrips_DimLocation]

Step 2: Alter the table

ALTER TABLE dbo.DimLocation

ADD StreetID NCHAR(10) NULL

Step 3: Truncate the table

TRUNCATE TABLE [dbo].[DimLocation]

Step 4: ADD Foreign keys

ALTER TABLE [dbo].[DimLocation] WITH CHECK ADD CONSTRAINT [FK_DimLocation_DimCity]

FOREIGN KEY ([CityKey]) References [dbo].[DimCity] ([CityKey])
GO

ALTER TABLE [dbo].[DimLocation] WITH CHECK ADD CONSTRAINT [FK_DimLocation_DimStreet]

FOREIGN KEY ([StreetID]) References [dbo].[DimStreet] ([Street_Code])
GO

ALTER TABLE [dbo].[FactTrips] WITH CHECK ADD CONSTRAINT [FK_FactTrips_DimLocation]
FOREIGN KEY ([LocationKey]) REFERENCES [dbo].[DimLocation] ([LocationKey])
GO

Step 5: Updated population logic

--The dimLocation Table, which would repopulate the table

INSERT INTO dbo.DimLocation
( CityKey, Street , StreetID)
(
   SELECT
       [CityKey] = DimCity.CityKey,
       [Street] = CAST ( isNull([StreetName], 'Unknown') AS NVARCHAR(50)),

[StreetID] = [Street_Code]
   FROM ([ServiceDB].[Dbo].[Street]
   INNER JOIN [DWService].[dbo].[DimCity]
   ON [serviceDB].[dbo].[Street].[City_Code] = [DWService].[dbo].[DimCity].[CityID]
   )
   )

So essentially after I truncate it, I would need to drop the table, recreate the talbe, add the FK's back in and then Populate it.
I just am not sure the script to Drop the table in this context

Explanation / Answer

Hi,

Before dropping the table, all the references(may be refered in views,procedures, and with other table as foreigh keys,etc) must be dropped/removed.

Step 1: Drop foreign keys for example
ALTER TABLE dbo.FactTrips DROP CONSTRAINT [FK_FactTrips_DimLocation]

Step 2: Truncate the table data

TRUNCATE TABLE [dbo].[DimLocation]

Step 3: Drop the table

DROP TABBLE If Exist dbo.DimLocation

If the dropped table needs to be recreated, then the dropped foreigh key must be recreated as well.

Step 4:

CREATE TABLE DimLocation
( location_id INT(11) NOT NULL AUTO_INCREMENT,
CONSTRAINT location_pk PRIMARY KEY (location_id),
);

Step 5: ADD Foreign keys

ALTER TABLE [dbo].[DimLocation] WITH CHECK ADD CONSTRAINT [FK_DimLocation_DimCity]

FOREIGN KEY ([CityKey]) References [dbo].[DimCity] ([CityKey])
GO

ALTER TABLE [dbo].[DimLocation] WITH CHECK ADD CONSTRAINT [FK_DimLocation_DimStreet]

FOREIGN KEY ([StreetID]) References [dbo].[DimStreet] ([Street_Code])
GO

ALTER TABLE [dbo].[FactTrips] WITH CHECK ADD CONSTRAINT [FK_FactTrips_DimLocation]
FOREIGN KEY ([LocationKey]) REFERENCES [dbo].[DimLocation] ([LocationKey])
GO

Step 6: populate data into dimlocation table

INSERT INTO dbo.DimLocation
( CityKey, Street , StreetID)
(
SELECT
[CityKey] = DimCity.CityKey,
[Street] = CAST ( isNull([StreetName], 'Unknown') AS NVARCHAR(50)),

[StreetID] = [Street_Code]
FROM ([ServiceDB].[Dbo].[Street]
INNER JOIN [DWService].[dbo].[DimCity]
ON [serviceDB].[dbo].[Street].[City_Code] = [DWService].[dbo].[DimCity].[CityID]
)
)

Step 7.update referrence tables with dimlocation data if application

Please comment.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.