|
MikeMiller
Starting Member
7 Posts |
 Posted - 2012-02-28 : 15:53:19
|
| Hello All!I’m a little new to SQL and hoping someone with broader knowledge then I can assist me with a little problem that I’m having.I have a poorly formatted file that comes in daily that I import into a one column table. From the one column table I parse into the final destination table. The next day another file comes in and will have the data that I already imported along with data that I have not yet imported. I obviously do not want to duplicate data therefore I only want to import the data that I do not have in my table. For other complications I cannot truncate the table before importing (that would be to easy right?) ?Below is what I put together but it’s not working right because it duplicates data. I’m sure it’s rather obvious what I’m missing to skilled eyes but mine aren’t so. ?BEGIN INSERT INTO xbr.tblRITransactionDetail (RITransactionID, PostingNumber, EntryCode, PolicyContractNumber, EffectiveDate, ClaimNumber, AccidentDate, NetDueDate, UnderwritingArea, FinancialProduct, FinancialCoverage, ProfitCenter, StatutoryProduct, ClassOfBusiness, RemittCategory, AmountInOriginalCurrency, Currency) SELECT @RITransactionID, Substring(File_RI, 2,4) AS PostingNumber, Substring(File_RI, 6,4) AS EntryCode, Substring(File_RI, 10,20) AS PolicyContractNumber, Substring(File_RI, 30,8) AS EffectiveDate, Substring(File_RI, 38,24) AS ClaimNumber, Substring(File_RI, 62,8) AS AccidentDate, Substring(File_RI, 70,8) AS NetDueDate, Substring(File_RI, 78,24) AS UnderwritingArea, Substring(File_RI, 102,10) AS FinancialProduct, Substring(File_RI, 112,4) AS FinancialCoverage, Substring(File_RI, 116,10) AS ProfitCenter, Substring(File_RI, 126,4) AS StatutoryProduct, Substring(File_RI, 130,24) AS ClassOfBusiness, Substring(File_RI, 154,3) AS RemittCategory, Substring(File_RI 157,17) AS AmountInOriginalCurrency Substring(File_RI, 174,5) AS Currency FROM dbo.Staging_File_RI ri2 left outer join xbr.tblRITransactionDetail rit2 ON Substring(ri2.File_RI, 2,4) = rit2.PostingNumber AND Substring(ri2.File_RI, 6,4) = rit2.EntryCode AND Substring(ri2.File_RI, 10,20) = rit2.PolicyContractNumber AND Substring(ri2.File_RI, 30,8) = rit2.EffectiveDate AND Substring(ri2.File_RI, 38,24)= rit2.ClaimNumber AND Substring(ri2.File_RI, 62,8) = rit2.AccidentDate AND Substring(ri2.File_RI, 70,8) = rit2.NetDueDate AND Substring(ri2.File_RI, 157,17)= rit2.AmountInOriginalCurrency AND Substring(ri2.File_RI, 78,24) = rit2.UnderwritingArea AND Substring(ri2.File_RI, 102,10) = rit2.FinancialProduct AND Substring(ri2.File_RI, 112,4) = rit2.FinancialCoverage AND Substring(ri2.File_RI, 116,10) = rit2.ProfitCenter AND Substring(ri2.File_RI, 126,4) = rit2.StatutoryProduct AND Substring(ri2.File_RI, 130,24) = rit2.ClassOfBusiness AND Substring(ri2.File_RI, 154,3) = rit2.RemittCategory WHERE id=@i AND rit2.PostingNumber IS NULL AND rit2.EntryCode IS NULL AND rit2.PolicyContractNumber IS NULL AND rit2.EffectiveDate IS NULL AND rit2.ClaimNumber IS NULL AND rit2.AccidentDate IS NULL AND rit2.AmountInOriginalCurrency IS NULL AND rit2.UnderwritingArea IS NULL AND rit2.FinancialProduct IS NULL AND rit2.FinancialCoverage IS NULL AND rit2.ProfitCenter IS NULL AND rit2.StatutoryProduct IS NULL AND rit2.ClassOfBusiness IS NULL AND rit2.RemittCategory IS NULL; |
|
Import unique rows assistance please