noobe1
asked on
MSSQL Loop thru entire table
I need to loop thru an entire table consisting of about 2million rows. I need to do some text matching routines on each row.
Is cursor a good option? What are my options besides using cursor?
Thanks
Is cursor a good option? What are my options besides using cursor?
Thanks
can you please clarify with data samples what you need to do, actually?
Use WHILE instead of cursor
WHILE <condition>
BEGIN
<query>
<set the operator>
END
//Check the following url's for reference:
//http://stackoverflow.com/questions/834582/while-loop-in-sql
//http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104176
WHILE <condition>
BEGIN
<query>
<set the operator>
END
//Check the following url's for reference:
//http://stackoverflow.com/questions/834582/while-loop-in-sql
//http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104176
For SQL Server 2005/2008, I would recommend either
(1) a set-based approach (if you can express the logic in a query fashion) or
(2) encapsulate the logic in a sql function, either as a scalar or generate rows using CROSS/OUTER APPLY
Even if you cannot process all 2 million records in one go, doing batches of 10,000 or so in a set will be more efficient by far then a straight cursor/loop one-by-one.
(1) a set-based approach (if you can express the logic in a query fashion) or
(2) encapsulate the logic in a sql function, either as a scalar or generate rows using CROSS/OUTER APPLY
Even if you cannot process all 2 million records in one go, doing batches of 10,000 or so in a set will be more efficient by far then a straight cursor/loop one-by-one.
ASKER
@angelIII:
Incoming data : Johar New Town Argon Binary
My table has 5 columns:
ID int
sName varchar(200) eg - John
sLoca varchar(200) eg London
sDeci varchar(200) eg Hydrocarbon
sGen varchar(200) eg Tri Penta
I will first need to concat the 4 varchar columns into :
John London Hydrocarbon Tri Penta
Then I run a text matching routine on "Johar New Town Argon Binary" with "John London Hydrocarbon Tri Penta" to get a percentage matched. I will need to compare the incoming data with 2 million rows of data I have in the table.
Thanks
Incoming data : Johar New Town Argon Binary
My table has 5 columns:
ID int
sName varchar(200) eg - John
sLoca varchar(200) eg London
sDeci varchar(200) eg Hydrocarbon
sGen varchar(200) eg Tri Penta
I will first need to concat the 4 varchar columns into :
John London Hydrocarbon Tri Penta
Then I run a text matching routine on "Johar New Town Argon Binary" with "John London Hydrocarbon Tri Penta" to get a percentage matched. I will need to compare the incoming data with 2 million rows of data I have in the table.
Thanks
ASKER
@s_chilkury:
Will look into that.
@cyberkiwi:
Do you think set-based approach in my situation??
Thanks
Will look into that.
@cyberkiwi:
Do you think set-based approach in my situation??
Thanks
group_concat emulation in sql server:
http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html
http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Remember SQL is build for Set based operations. Set-based operations always outperform the Row by row approach. Cursors are Evil! you can avoid by using correlated sub-queries, or by using CTE(For SQL 2005 and Higher) or prefer using table variables or temp tables(you can create Indexes on it) and while loops.
First keep the concatenated value John London Hydrocarbon Tri Penta in a temp table and then CROSS JOIN it with main table on some ID field and get the matching percentage by using DIFFERENCE('string1', 'steing2') function.
If I am not wrong, the procedure you are trying to achieve is a massive programming, and this is a product of IBM called as Quality Stage, which is used for customer and address validation. First of all you have to take the individual percentage for the values entered in to the system, i mean using the occurrence methodology.
first, take the individual comparison values and union then, into a temp table.
sName where occurance are > 0 for the values ie "Johar"
sLoca where occurance are > 0 for the values ie "New Town"
sDeci where occurance are > 0 for the values ie "Argon"
sGen where occurance are > 0 for the values ie "Binary"
now combine the strings and compare with the concatenated string to get the percentage.
first, take the individual comparison values and union then, into a temp table.
sName where occurance are > 0 for the values ie "Johar"
sLoca where occurance are > 0 for the values ie "New Town"
sDeci where occurance are > 0 for the values ie "Argon"
sGen where occurance are > 0 for the values ie "Binary"
now combine the strings and compare with the concatenated string to get the percentage.