Insert Into inserts more records than it should

Transact-SQL
1

I import data using openrowset from excel into a table called tmpData.
After the openrowset import tmpData has 1314 records in it. I then use the following query to insert the data into table RacingMaster

use RacingData

INSERT INTO RacingMaster(Finishing_Position,RM_Draw,Distance_Beaten,RM_Horse_FK,RM_Trainer_FK, Age,Horse_Weight,Hd_Gear,RM_Jockey_FK, Allowance,RM_SP,Favourite,Horse_Finishing_Time,Group_Race,Listed,Rated,Conditions,Classified,Handicap,Nursery,Stakes,Maiden,Selling,Claiming,Apprentice,Amateur,Race_Class,Dist_Of_Race_FK,Going,RM_Race_Date,RM_Course_FK,Race_Runners,Race_Time)

SELECT tmpFinishPos,tmpDraw,tmpDistBtn,Horses.PKHorseId,Trainers.PKTrainerId,tmpAge,tmpHWeight,tmpHdGear,Jockeys.PKJockeyId,tmpAllowance,tmpSP,tmpFav,tmpHorseTime,tmpgroup,tmplisted,tmplisted,tmpconditions,tmpclassified,tmphandicap,tmpnursery,tmpstakes,tmpmaiden,tmpselling,tmpclaiming,tmpapprentice,tmpamateur,tmpraceclass,RaceDistances.PKRaceDistancesId,tmpgoing,tmpracedate,Courses.PKCourseId,tmprunners,tmpracetime

FROM tmpData
LEFT JOIN Horses ON Horses.HorseName= tmpData.HorseName
LEFT JOIN Trainers ON Trainers.TrainerName = tmpData.TrainerName
LEFT JOIN Jockeys ON Jockeys.JockeyName = tmpData.JockeyName
LEFT JOIN Courses ON Courses.CourseName = tmpData.tmpcourse
LEFT JOIN RaceDistances on RaceDistances.RaceDistance=tmpData.tmpracedistance

However rather than the expected 1314 rows, after the insert RacingMaster contains 1680 rows.
Can anyone please explain why this should be? I just want to apologise for the formatting, but the procedure does work so I don;t think it is a syntax thing or a column mismatch.

2

This can happen if one of the joins has two or more rows for some of the rows in the rowset. Since they are cumulative, you need to do each join separately to see where the extra rows come in.

1 Like
3

Thank you for your prompt response. I think that is the cause, but I am not sure how I would implement your suggested solution. The table RacingMaster requires the joined tables, Horses, Trainers, Jockeys, Courses and RaceDistances to provide a unique int as the Primary Key. How would I insert into RacingMaster and exclude any of the values for the joined tables?

4

An incremental approach:

change the explicit column list to * (not for production! Just for testing)
add the joins one at a time in dependency order
observe when the row counts go higher than expected
troubleshoot THAT join

5

Finally got around to resolving the problem I had and you were right, the joins were of the wrong sort, Thank you for your help.