I got it to work with the new scenario. I wasn't sure how to deal with multiple mapping tables but realized that it really is no different.
For the new scema I did the following query with an insert where the "WHERE" clause inserts only those not already in the table. Worked perfectly.
I knew what a CROSS JOIN was but had always wondered why you would ever need or use one. And know I see a use for it.
I was also trying to figure out which table to put the cross join on and then realized that it didn't make any difference.
So the thought process is:
1) Do a join to get all the possible records
2) Left Join the table you want the records to go into
3) Filter out the non nulls
SELECT *
FROM (SELECT *
FROM DealerForecast f
CROSS JOIN Models m) mdf
LEFT OUTER JOIN DealerForecastModel dfm
ON mdf.DealerForecastId = dfm.DealerForecastId AND mdf.ModelID = dfm.ModelID
WHERE DealerForecastModelID IS NULL
The Schema I used was:
ALTER PROC [dbo].[CreateSchema]
AS
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'DealerForecastModel'))
DROP Table DealerForecastModel
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'DealerForecast'))
DROP Table DealerForecast
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'ModelForecast'))
DROP Table ModelForecast
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Dealers'))
DROP Table Dealers
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Models'))
DROP Table Models
IF (EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Forecast'))
DROP Table Forecast
CREATE TABLE [dbo].[Dealers](
[DealerID] [int] CONSTRAINT PK_Dealers Primary Key,
[DealerCode] [varchar](15) NOT NULL,
[DealerName] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Models](
[ModelID] [int] CONSTRAINT PK_Models Primary Key,
[ModelCode] [varchar](15) NOT NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[Forecast](
[ForecastId] [int] CONSTRAINT PK_Forecast Primary Key,
[Name] [varchar](50) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[DealerForecast](
[DealerForecastID] [int] CONSTRAINT PK_DealerForecast Primary Key,
[DealerID] [int] NULL,
[ForecastID] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ModelForecast](
[ModelForecastID] [int] CONSTRAINT PK_ModelForecast Primary Key,
[ModelID] [int] NULL,
[ForecastID] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[DealerForecastModel](
[DealerForecastModelID] [int] CONSTRAINT PK_DealarForecastModel Primary Key,
[DealerForecastID] [int] NULL,
[ModelID] [int] NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[DealerForecastModel] WITH CHECK ADD CONSTRAINT [FK_DealerForecastModel_DealerForecast] FOREIGN KEY([DealerForecastID])
REFERENCES [dbo].[DealerForecast] ([DealerForecastID])
ALTER TABLE [dbo].[DealerForecastModel] WITH CHECK ADD CONSTRAINT [FK_DealerForecastModel_Models] FOREIGN KEY([ModelID])
REFERENCES [dbo].[Models] ([ModelID])
ALTER TABLE [dbo].[DealerForecast] WITH CHECK ADD CONSTRAINT [FK_DealerForecast_Dealers] FOREIGN KEY([DealerID])
REFERENCES [dbo].[Dealers] ([DealerID])
ALTER TABLE [dbo].[DealerForecast] WITH CHECK ADD CONSTRAINT [FK_DealerForecast_Forecast] FOREIGN KEY([ForecastID])
REFERENCES [dbo].[Forecast] ([ForecastId])
ALTER TABLE [dbo].[ModelForecast] WITH CHECK ADD CONSTRAINT [FK_ModelForecast_Forecast] FOREIGN KEY([ForecastID])
REFERENCES [dbo].[Forecast] ([ForecastId])
ALTER TABLE [dbo].[ModelForecast] WITH CHECK ADD CONSTRAINT [FK_ModelForecast_Models] FOREIGN KEY([ModelID])
REFERENCES [dbo].[Models] ([ModelID])
INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(1, '11', 'Brea')
INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(2, '05', 'Fontana')
INSERT Dealers(DealerID, DealerCode, DealerName) VALUES(3, '08', 'Cerritos')
INSERT Models(ModelID, ModelCode) VALUES(1, 'YZ-125')
INSERT Models(ModelID, ModelCode) VALUES(2, 'YZ-250')
INSERT Models(ModelID, ModelCode) VALUES(3, 'YZ-450')
INSERT Models(ModelID, ModelCode) VALUES(4, 'Rhino 700')
INSERT Models(ModelID, ModelCode) VALUES(5, 'Grizzly 450')
INSERT Models(ModelID, ModelCode) VALUES(6, 'FZR')
INSERT Models(ModelID, ModelCode) VALUES(7, 'FZS')
INSERT Forecast(ForecastID, Name) VALUES(4, 'Motocross')
INSERT Forecast(ForecastID, Name) VALUES(5, 'ATV')
INSERT Forecast(ForecastID, Name) VALUES(6, 'Watercraft')
INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(1, 1, 4)
INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(2, 2, 4)
INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(3, 3, 4)
INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(4, 4, 5)
INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(5, 5, 5)
INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(6, 6, 6)
INSERT ModelForecast(ModelForecastID, ModelID, ForecastID) VALUES(7, 7, 6)
INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(1, 1, 4)
INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(2, 1, 5)
INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(3, 1, 6)
INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(4, 2, 4)
INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(5, 2, 5)
INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(6, 2, 6)
INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(7, 3, 4)
INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(8, 3, 5)
INSERT DealerForecast(DealerForecastID, DealerID, ForecastID) VALUES(9, 3, 6)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(1, 1, 1)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(2, 1, 3)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(3, 2, 1)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(4, 2, 2)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(5, 2, 6)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(6, 3, 3)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(7, 3, 4)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(8, 4, 6)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(9, 5, 2)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(10, 5, 4)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(11, 5, 6)
INSERT DealerForecastModel(DealerForecastModelID, DealerForecastID, ModelID) VALUES(12, 5, 7)
Thanks for all the help,
Tom