SQL Server Performance Forum – Threads Archive
Help Regarding Migration logici 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
091,1021 Then the destination table should look like AreaRangeFromRangeTo
09110211021 Please not that the ranges are merged.
Now if next time my csv file contains 090,1003
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
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.
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. ***********************
SQL Server MVP
thanks for replying dinakar. I am using queries on my destination table directly not on my csv. Thanks