October 17, 2012 at 12:46 pm
Folks:
I need help with a While loop sql query. I have a table (#tblGroups) which stores our Active Directory users. Using the data from this table I insert records into another table (#tblTest). I use the below query but for some reason the last record in the #tblGroups doesn't get inserted into #tblTest table. Any help on modifying the SQL query or table structure will be helpful.
CREATE TABLE #tblGroups (CN VARCHAR(128), DN VARCHAR(1024));
INSERT INTO #tblGroups values ('TRAccess','CN=TRAccess,OU=Groups - Access,OU=Groups,DC=XYZ,DC=com')
INSERT INTO #tblGroups values ('VIUsers','CN=VIUsers,OU=Groups - Functional,OU=Groups,DC=XYZ,DC=com')
INSERT INTO #tblGroups values ('VPAccess','CN=VPAccess,OU=Groups - Access,OU=Groups,DC=XYZ,DC=com')
INSERT INTO #tblGroups values ('VUAccess','CN=VUAccess,OU=Groups - Access,OU=Groups,DC=XYZ,DC=com')
CREATE TABLE #tblTest (AccountName VARCHAR(200), SecurityGroup VARCHAR(1024))
DECLARE @CN VARCHAR(128)
DECLARE @DN VARCHAR(1024)
SELECT TOP 1 @CN = CN, @DN = DN FROM #tblGroups
WHILE EXISTS(SELECT DN FROM #tblGroups WHERE DN > @DN)
BEGIN
INSERT INTO #tblTest
SELECT @CN, @DN
SELECT TOP 1 @CN = CN, @DN = DN FROM #tblGroups WHERE DN > @DN
END
SELECT * FROM #tblGroups
SELECT * FROM #tblTest
October 17, 2012 at 1:24 pm
Nevermind.... I figured it out. Added a ID column and use the ID in the while.
SELECT @RecordID = MIN(ID) from #tblGroups
WHILE @RecordID IS NOT NULL
BEGIN
October 17, 2012 at 1:30 pm
Why do you need a while loop?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2012 at 1:44 pm
Gail is totally right. There is no need for a while loop here at all. All you are doing is copying a table to another table.
insert #tblTest
select cn, dn
from #tblGroups
Not only is the while loop a potentially slow process the way you coded it you are going to get incorrect results sometimes. You have used select top with no order by not once but twice in this process. There is no guarantee what order your rows will be returned if you don't use an order by.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply