October 23, 2018 at 3:55 pm
Hi all,
I have a need to read every record in a table and compare a name in that table to another name.
In Access VBA, I would loop through each record in a table to read the name.
It's very simple:Dim i as long ' declaration
Dim mystring as string ' declaration
Dim recordset as DAO.recordset
Set recordset = currentdb.openrecordset("MyTable", dbOpenSnapshot)
recordset.movefirst
For i = 1 to recordset.recordcount
mystring = recordset![FeildName]
recordset.movenext
Next i
This is a very simplistic way to do it, but that's generally it. So how can I do this exact same thing in SQL server?
Can somebody offer some easy to understand advice about how to best loop through each record.
Also, complete SQL noob here.
Cheers
October 23, 2018 at 4:36 pm
barry.nielson - Tuesday, October 23, 2018 3:55 PMHi all,
I have a need to read every record in a table and compare a name in that table to another name.
In Access VBA, I would loop through each record in a table to read the name.
It's very simple:Dim i as long ' declaration
Dim mystring as string ' declaration
Dim recordset as DAO.recordset
Set recordset = currentdb.openrecordset("MyTable", dbOpenSnapshot)
recordset.movefirst
For i = 1 to recordset.recordcount
mystring = recordset![FeildName]
recordset.movenext
Next i
This is a very simplistic way to do it, but that's generally it. So how can I do this exact same thing in SQL server?
Can somebody offer some easy to understand advice about how to best loop through each record.
Also, complete SQL noob here.
Cheers
When moving to SQL from VBA or any other procedural language, you need to learn to think in sets. If I read the code above correctly you are looping through a record set (perhaps a result set from SQL) selecting a specific column. In SQL you would simply do this:
SELECT
YourColumn
FROM
dbo.YourTable;
October 23, 2018 at 4:45 pm
Thanks Lynn,
I understand this is how to select a column. I'm interested in learn how to incrementally select each record in that column... that is, to return the value contained in that column.
Now further to this query, I have found the following and modified it slightly
-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT ([Field Name]) FROM MyTable)
-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1
-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @iUserName VARCHAR(255)
-- Get the data from table and set to variables
SELECT @iUserName = [Field Name] FROM MyTable Where @I = ???? --limit of my understanding
-- Display the looped data
PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I) + ' - UserName = ' + @iUserName
-- Increment the iterator
SET @I = @I + 1
END
Now this is not quite there yet, because I don't know how to count the row number I am currently reading. It feel like the right direction, unless somebody recognise my error and can steer me off the rocks.
October 23, 2018 at 4:52 pm
barry.nielson - Tuesday, October 23, 2018 4:45 PMThanks Lynn,
I understand this is how to select a column. I'm interested in learn how to incrementally select each record in that column... that is, to return the value contained in that column.Now further to this query, I have found the following and modified it slightly
-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT ([Field Name]) FROM MyTable)
-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1
-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @iUserName VARCHAR(255)
-- Get the data from table and set to variables
SELECT @iUserName = [Field Name] FROM MyTable Where @I = ???? --limit of my understanding
-- Display the looped data
PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I) + ' - UserName = ' + @iUserName
-- Increment the iterator
SET @I = @I + 1
ENDNow this is not quite there yet, because I don't know how to count the row number I am currently reading. It feel like the right direction, unless somebody recognise my error and can steer me off the rocks.
Big question, why? SQL Server isn't an interactive system.
October 23, 2018 at 5:04 pm
It's part of a wider series of functions.
I have a table which produces around 10 records, each is a mispelt name. I need to compare each of these name with a larger customer name list, and in that comparison return a correct spelling that appears to match to within 90% or so. Now I have the function that does this check. What I don't have is a way to read the larger table and compare a spelling against each customer name from my list of misspelt names.
So maybe I'm going the wrong way about it, but my philosophy is to make it work before I make it pretty.
October 23, 2018 at 5:52 pm
October 23, 2018 at 6:11 pm
Thank you pietlinden, but in both of those examples, you are required to provide a SourceString and a TargetString. The point of my requirement was to collect a target string from a table by iterating through it, such that it could be fed into an algorithm such as these.
I eventually discovered this following code, which is fast and functions. I don't claim to understand every line as I do VBA, but I get the general idea.
--Cursor Method. Iterate through each record and return the required record info.
DECLARE @CurrentName [nvarchar](255), @ReturnedClient [nvarchar](255)
DECLARE @CurrentID INT, @iCurrentValue INT, @iHighestValue INT
DECLARE CurName CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT [ID], [Field Name]
FROM DBO.MyTable
OPEN CurName
FETCH NEXT FROM CurName INTO @CurrentID, @CurrentName
WHILE @@FETCH_STATUS = 0
BEGIN
--Print an Integer and a string on the same line
Print Cast(@CurrentID as Varchar) + ' - ' + ( @CurrentName)
-- Match the Current name against a requested name, by feeding into LevenshteinDistance algorithm.
--Move to next record
FETCH NEXT FROM CurName INTO @CurrentID, @CurrentName
END
CLOSE CurName
DEALLOCATE CurName
October 23, 2018 at 7:58 pm
Just remember that cursor code is RBAR (Row By Agonizing Row) and will not scale as well as well written set-base code.
October 23, 2018 at 8:25 pm
Thanks for the warning Lynn.
As I said, I want it to work before I make it pretty. So far, I have not found a method that does what I need, and despite it's flaws it does what I need to to do.
I have so far not found a method by which I can compare an entire column or set against a single variable. if you know of one, please let me know.
Thanks again for your input and comments.
Cheers
October 23, 2018 at 8:52 pm
In a nutshell, you're trying to do a fuzzy match between two columns of data? What version of SQL 2008 are you using? (I think fuzzy lookups in SSIS are enterprise-only, but MS keeps moving stuff around!)
Maybe these will help:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ae08d4d8-fb25-48d2-97c7-168d0caff428/matching-similar-sounding-names-in-sql-server?forum=transactsql
http://www.sqlservercentral.com/articles/Fuzzy+Match/92822/
Yeah, I know one is by Lynn's favorite guy on the Internet... <ducking>
October 23, 2018 at 10:35 pm
barry.nielson - Tuesday, October 23, 2018 5:04 PMIt's part of a wider series of functions.
I have a table which produces around 10 records, each is a mispelt name. I need to compare each of these name with a larger customer name list, and in that comparison return a correct spelling that appears to match to within 90% or so. Now I have the function that does this check. What I don't have is a way to read the larger table and compare a spelling against each customer name from my list of misspelt names.So maybe I'm going the wrong way about it, but my philosophy is to make it work before I make it pretty.
You could try something like this ...
If your function is a Table Valued Function SELECT src.[ID], src.[Field Name], fn.[Answer]
FROM dbo.MyTable AS src
CROSS APPLY dbo.YourLookupFunction(src.[Field Name]) AS fn;
If your function is a Scalar Function SELECT src.[ID], src.[Field Name]
, dbo.YourLookupFunction(src.[Field Name]) AS [Answer]
FROM dbo.MyTable AS src;
October 24, 2018 at 8:18 am
barry.nielson - Tuesday, October 23, 2018 8:25 PMThanks for the warning Lynn.
As I said, I want it to work before I make it pretty. So far, I have not found a method that does what I need, and despite it's flaws it does what I need to to do.
I have so far not found a method by which I can compare an entire column or set against a single variable. if you know of one, please let me know.
Thanks again for your input and comments.
Cheers
Only way to really help is for you to post the DDL (CREATE TABLE statement) for each of the table(s) involved, sample data (not production data) as INSERT statements (look at using Table Value Constructors, not individual or unioned inserts), and the expected results based on the sample data provided.
October 24, 2018 at 2:02 pm
barry.nielson - Tuesday, October 23, 2018 4:45 PMThanks Lynn,
I understand this is how to select a column. I'm interested in learn how to incrementally select each record in that column... that is, to return the value contained in that column.Now further to this query, I have found the following and modified it slightly
-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT ([Field Name]) FROM MyTable)
-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1
-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @iUserName VARCHAR(255)
-- Get the data from table and set to variables
SELECT @iUserName = [Field Name] FROM MyTable Where @I = ???? --limit of my understanding
-- Display the looped data
PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I) + ' - UserName = ' + @iUserName
-- Increment the iterator
SET @I = @I + 1
ENDNow this is not quite there yet, because I don't know how to count the row number I am currently reading. It feel like the right direction, unless somebody recognise my error and can steer me off the rocks.
Hey Barry I modified your query. I hope that helps with your original question re:how to incrementally select each record in that column
DECLARE @mytable Table(id int identity, fname varchar(100))
INSERT @mytable
SELECT [Field Name]
FROM
(
VALUES
('Alex'), ('Bob'), ('Chris'), ('Diana'), ('Eve'), ('Frank'), ('George'), ('Harry'), ('Ian'), ('Jane')
) AS T1([Field Name])
/*YOU MAY SELECT THE NAMES FROM YOUR TABLE BELOW
JUST COMMENT OUT THE PREVIOUS SELECT STATEMENT ABOVE THAT IS JUST FOR ILLUSTRATION
*/
--SELECT [Field Name] FROM MyTable
select * from @mytable
-- Get the number of rows in the looping table
DECLARE @RowCount INT
--SET @RowCount = (SELECT COUNT ([Field Name]) FROM @MyTable)
SET @RowCount = (SELECT COUNT (*) FROM @mytable)
-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1
-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @iUserName VARCHAR(255)
-- Get the data from table and set to variables
--SELECT @iUserName = [Field Name] FROM MyTable Where @I = ???? --limit of my understanding
SELECT @iUserName = fname FROM @mytable Where @I = id --limit of my understanding (**you need an identity field to iterate through***)
-- Display the looped data
PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I) + ' - UserName = ' + @iUserName
SELECT 'Row No = ' + CONVERT(VARCHAR(2), @I) + ' - UserName = ' + @iUserName
-- Increment the iterator
SET @I = @I + 1
END
October 24, 2018 at 2:15 pm
From your narrative, I wouldn't do this at all in SQL. I'd buy a mailing list scrubbing package, like Melissa Data, and not reinvent the wheel (badly reinvent).
Please post DDL and follow ANSI/ISO standards when asking for help.
October 24, 2018 at 5:20 pm
barry.nielson - Tuesday, October 23, 2018 3:55 PMSQL is not a procedural language; it's declarative. That means we don't have loops, we don't have if-then-else or any other control flow statements. You have to get out of SQL, use a cursor and convert the table into a file. A file has records, which are nothing whatsoever like the rows in a table. Basically, what you're asking is how do we alphabetize Chinese. The concept doesn't exist in the language you're using. And it's not a matter of making it pretty; your whole mindset is completely wrong. You're the flat Earth kid in an astronomy class 🙁From your narrative, I wouldn't do this at all in SQL. I'd buy a mailing list scrubbing package, like Melissa Data, and not reinvent the wheel (badly reinvent).
While I agree with most of what you said, why do you almost always have to resort to ad hominem attacks and passive aggressive school boy snips? And, no... that's a rhetorical couple of questions. I've heard your answer to those questions before and they also need serious improvement in the mentor area. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply