This is the case:
I have a table and basically this table has many redundancy. The solution is to create a link table and this table will contains record association. So what I need to do is to get all the ID’s Populated to the link table and link it to a single unique record on the original table. The logic that I used is to get the value from the column based on the lowest ID.
INSERT INTO tblExpReceiptFileAssociation(intExpenseDtlId, intFileID) SELECT te.intExpenseDtlID, ta.LinkFileID FROM tblExpReceiptFile te INNER JOIN ( SELECT vcFileName, bintFileSize, chCreateStaffCode, CONVERT(VARCHAR,sdCreateDate,101) as DateCreated, MIN(intFileID) as LinkFileID FROM tblExpReceiptFile GROUP BY vcFileName, bintFileSize, chCreateStaffCode, CONVERT(VARCHAR,sdCreateDate,101) ) ta ON te.vcFileName = ta.vcFileName AND te.bintFileSize = ta.bintFileSize AND te.chCreateStaffCode = ta.chCreateStaffCode AND CONVERT(VARCHAR,te.sdCreateDate,101) = ta.DateCreated