SQL Server Performance Forum – Threads Archive
Need help with Checksum ProcedureMorning Campers, I have two tables src_monthly_terrrier and src_weekly_terrier. Both of these tables consists of 10+ columns. As the table names probably suggest, I import weekly data into one and monthly data into another. All the source data comes from an Excel spreadsheet via straight Import Data procedure. The only guaranteed change on a weekly and monthly basis is that one of the columns in each table named src_date will obviously have the data value for whichever month or week’s data it relates to. I understand that through ‘SQL Server Business Intelligence Development Studio’ I can create an ‘Intergrated Services’ package that will import the spreadsheet details for me. I might be going the long way around this, but it was my intention to bring in all the data and then run a couple of ‘INSERT INTO’ Stored Procedures. My biggest issue / vunerability I have is that there is no error checking of the data on the way in to ensure that it has not already been imported. What I was thinking I could do to resolve this was to create a Checksum field comprising of a number of different columns (incl src_date) and then somehow write something that will look at the values of each intended imported row and then work out whether a duplicate checksum was found in the target table and then rejected the import routine as Duplicate Data Found (or something similar) and move onto the next stored procedure. My problem is two fold, one I have no idea how to create said checksum and two no idea where to begin on coding a procedure etc that looks to see if the value already exists etc etc. I have looked up checksum creation on the net and there appears to be plenty of resource to explain how to create one, so I guess my main question is, Where do I start when it comes to writing some code that will do the check of the checksum before the importation routine begins (or at least the Insert Into procedures. I would truly appreciate anyone’s help on this. In the meanwhile I am off to learn how to create them. I would like to add, if anyone sees this as a bad idea, then please speak up. Thanks in Advance
http://www.umachandar.com/technical/SQL2000Scripts/Main13.htm Fyiu…HTH Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
Spooky, when I said there was stuff on the net, the one I was referring to is the first one on your list of suggestions. Thanks
Toni, it seems your doing the data warehouse ETL types (either Type1, Type2 or Type3). My suggestion is to have a load table where you can dump the records and compute the checksum. You can then use that checksum to verify if it exists. If not, only then you insert it in the production table. Check the different data warehouse ETL I suggested because it handles different scenario of how to perform insert overtime (like person table with person name changes). May the Almighty God bless us all!
Hi cmdr. I shall look up what you are talking about in terms of warehousing. I agree that perhaps a staging area is a better move. I hope that whatever I find on the net will tell me how to do it. SQL 2005 – Baptism by fire this feels like. Kindest Regards
let us know so and we’ll be glad to help you <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />. After all, I am not alone here <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />.<br /><br />May the Almighty God bless us all!<br />www.empoweredinformation.com