Please remember that DimLocation is already populated with data and may also have a PK-FK relationship specified. Therefore, take appropriate steps to empty out the table, make the change, and then repopulate the table with data. I am not sure how to empty a single table on its own, how would I be able to do that?

INSERT INTO dbo.DimLocation ( CityKey, Street ) ( SELECT [CityKey] = DimCity.CityKey, [Street] = CAST ( isNull([StreetName], 'Unknown') AS NVARCHAR(50)) FROM ([ServiceDB].[Dbo].[Street] INNER JOIN [DWService].[dbo].[DimCity] ON [serviceDB].[dbo].[Street].[City_Code] = [DWService].[dbo].[DimCity].[CityID] ) )

This is the code I have to create the DWService: OLAP

USE [DWService] GO

--Create DimDriver CREATE TABLE [dbo].[DimDriver] ( [DriverKey] int Not null Identity, [DriverID] [nchar](8) , [DriverName] [nvarchar](100) CONSTRAINT [PK_DimDriver] PRIMARY KEY CLUSTERED ([DriverKey] ASC) ) GO

-- Create Dimension table for dates CREATE TABLE [dbo].[DimDates] ( [DateKey] int NOT NULL Identity , [Date] datetime NOT NULL , [DateName] nVarchar(50) , [Month] int NOT NULL , [MonthName] nVarchar(50) NOT NULL , [Year] int NOT NULL , [YearName] nVarchar(50) NOT NULL CONSTRAINT [PK_DimDates] PRIMARY KEY CLUSTERED ([DateKey] ASC)) GO

--Create DimCity CREATE TABLE [dbo].[DimCity]( [CityKey] [int] NOT NULL Identity, [CityID] nchar(10), [CityName] nchar(50) , [State] nVarChar(50) , CONSTRAINT [PK_DimCity] PRIMARY KEY CLUSTERED ([CityKey] ASC)) GO

--[CityID] [Nchar](10) NOT NULL, --[CityName] [NVarChar](50) NOT NULL,

CREATE TABLE [dbo].[DimLocation] ( [LocationKey] [int] NOT NULL IDENTITY, [CityKey] [int] , [Street] [NVARCHAR](50) NOT NULL CONSTRAINT [PK_DimLocation] PRIMARY KEY CLUSTERED ([LocationKey] ASC)) GO

CREATE TABLE [dbo].[FactTrips] ( [TripNumber] [NVarchar](50) NOT NULL, [DateKey] [int] NOT NULL, [LocationKey] [int] NOT NULL, [DriverKey] [int] NOT NULL, [TripMilage] [decimal](18,4) NOT NULL, [TripChange] [decimal](18,4) NOT NULL,

CONSTRAINT [PK_FactTrips] PRIMARY KEY CLUSTERED ([TripNumber] ASC, [DateKey] ASC, [LocationKey] ASC, [DriverKey] ASC ) ) GO

ALTER TABLE [dbo].[DimLocation] WITH CHECK ADD CONSTRAINT [FK_DimLocation_DimCity] FOREIGN KEY ([CityKey]) References [dbo].[DimCity] ([CityKey]) GO

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

ALTER TABLE [dbo].[FactTrips] WITH CHECK ADD CONSTRAINT [FK_FactTrips_DimDates] FOREIGN KEY ([DateKey]) References [dbo].[DimDates] ([DateKey]) GO

ALTER TABLE [dbo].[FactTrips] WITH CHECK ADD CONSTRAINT [FK_FactTrips_DimDriver] FOREIGN KEY ([DriverKey]) References [dbo].[DimDriver] ([DriverKey]) GO

This is the code to create serviceDB: OLTP

USE [serviceDB] GO

/****** Create the trip Table ******/ CREATE TABLE [dbo].[Trip]( [number] char(10) NOT NULL, [Date] [datetime] NOT NULL, [charge] [decimal](18,2), [milage] [decimal](18,2), [payment_no] int, [Street_Code] [nchar](10), [Driver_Id] [nchar](5), CONSTRAINT [PK_Trip] PRIMARY KEY CLUSTERED ( [number] ASC ))

/****** Create the Street Table ******/ CREATE TABLE [dbo].[Street]( [Street_Code] nchar(10) NOT NULL, [StreetName] nchar(50), [Zipcode] char(5), [City_Code] nchar(5), CONSTRAINT [PK_Street] PRIMARY KEY CLUSTERED ( [Street_Code] ASC ))

/****** Create the City Table ******/ CREATE TABLE [dbo].[City]( [City_Code] nchar(5) NOT NULL, [CountryName] nchar(50), CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED ( [City_Code] ASC ))

/****** Create the Driver Table ******/ CREATE TABLE [dbo].[Driver]( [Driver_Id] nchar(5) , [LastName] char(10), [FirstName] char(10), DateOfBirth datetime CONSTRAINT [PK_Driver] PRIMARY KEY CLUSTERED ( [Driver_Id] ASC ))

-- Creating the weatherDB foreign key constraints

ALTER TABLE [dbo].[Trip] WITH CHECK ADD CONSTRAINT [FK_Trip_Street] FOREIGN KEY([Street_Code]) REFERENCES [dbo].[Street] ([Street_Code]) GO

ALTER TABLE [dbo].[Trip] WITH CHECK ADD CONSTRAINT [FK_Trip_Driver] FOREIGN KEY([Driver_Id]) REFERENCES [dbo].[Driver] ([Driver_Id]) GO

ALTER TABLE [dbo].[Street] WITH CHECK ADD CONSTRAINT [FK_Street_City] FOREIGN KEY([City_Code]) REFERENCES [dbo].[City] ([City_Code]) GO