May 11, 2006 at 2:16 am
Morning 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
May 11, 2006 at 5:46 am
Admin, please delete, posted in wrong section
Apologies and Regards
Tonic
May 15, 2006 at 7:51 am
This isn't necessarily the wrong section..?
As for checksum goes - you have to be aware that checksums aren't 100% failsafe.
There is always the possibility that although there are different values in some columns, the checksum generated can still be the same.
A much better way would be to compare actual values.
Do you have anything in your data that serves as a primary key?
Presumably there is, and perhaps you can use that (or those) in conjunction with the weeknumber to make a key. Once you've established the key, import the new file into a staging table. After that it's pretty straightforward to check if you've already imported that file or not compared to your monthly table.
/Kenneth
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply