Help Regarding Migration logic

i have to migrate data from a csv file to a SQL server table. The csv file will contain 2 fields area and number. The destination table will have 3 columns : Area, Range From and Range To. The logic behind the migration is to uplaod the numbers for the sam Area in a Range rather than individual rows in the database.

For Example if csv file looks like below:

090,1001
090,1002
090,1003
090,1004
090,1009
090,1010
090,1011
091,1001
091,1021



Then the destination table should look like

AreaRangeFromRangeTo
09010011004
09010091011
09110011001
09110211021

Please not that the ranges are merged.


Now if next time my csv file contains

090,1003
090,1004
090,1005
090,1006
090,1007
090,1008
091,1000
091,1002



Then for the area 090 and number 1003 and 1004 these rows should be ignored as they already exist in the range 1001-1004 in the destination table. The other rows from 1005 to 1008 will be merged to the existing ranges in the table 1001-1004 and 1009-1011 making it 1001-1011 makin the tbale look like

AreaRangeFromRangeTo
09010011011
09110001002
09110211021


The logic that i am using right now is to have a OLE DB command component as the destination which first searches the table to check if the range already exists. If it exists it ignores the row. If it finds that the inpout row can form a new range it first deletes that range from the table and inser the new row with new RangeFrom and RangeTo. This process that i am using is taking a lot of time as there are about millions of rows to be migrated.

Can anyone suggest some better logic or way to accomplish the same so that upload time could be reduced.




Thanks
Freak
http://techyfreak.blogspot.com

I think one solution is to get all the data in the CSV into a staging table as-is. Then write a WHILE loop to loop through each record, storing the previous values and comparing against each other. Its easier to write queries on tables than against a text file. You can make this stored proc generic enough so it always goes through the staging table and updates your main table. Remember to clean up the stating table once processing is done.

***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

thanks for replying dinakar. I am using queries on my destination table directly not on my csv.

Thanks
Freak
http://techyfreak.blogspot.com