SQL Server 2008 Is there a more efficient way to do this upgrade cycle?
First question posted, I apologize in advance for any mistakes. The table contains the records assigned to the command, the initial assignments are performed by another process. Often we have to reassign agent records and distribute them equally to the rest of the team. We did it manually, one by one, which was cumbersome. So I came up with this solution:
DECLARE @UpdtAgt TABLE (ID INT, Name varchar(25))
INSERT INTO @UpdtAgt
VALUES (1, 'Gandalf')
,(2,'Hank')
,(3,'Icarus')
CREATE TABLE #UpdtQry (TblID varchar(25))
INSERT INTO #UpdtQry
SELECT ShtID
FROM TestUpdate
DECLARE @RowID INT
DECLARE @AgtID INT
DECLARE @Agt varchar(25)
DECLARE @MaxID INT
SET @MaxID = (SELECT COUNT(*) FROM @UpdtAgt)
SET @AgtID = 1
--WHILE ((SELECT COUNT(*) FROM #UpdtQry) > 0)
WHILE EXISTS (SELECT TblID FROM #UpdtQry)
BEGIN
SET @RowID = (SELECT TOP 1 TblID FROM #UpdtQry)
SET @Agt = (SELECT Name FROM @UpdtAgt WHERE ID = @AgtID)
UPDATE TestUpdate
SET Assignment = @Agt
WHERE ShtID = @RowID
DELETE #UpdtQry WHERE TblID = @RowID
IF @AgtID < @MaxID
SET @AgtID = @AgtID + 1
ELSE
SET @AgtID = 1
END
DROP TABLE #UpdtQry
This is really my first attempt at doing something like this. A 100 line update takes about 30 seconds. The UPDATE table, TestUpdate, has only a CLUSTERED index. How can I make this more efficient?
EDIT: I didn't define the @UpdtAgt and #UpdtQry tables very well in my explanations. @UpdtAgt will contain agents that are reassigning entries, and will likely change every time this is used. #UpdtQry will have a WHERE clause to determine which agent records will be reassigned, again this will change on every use. Hopefully this becomes even clearer. Again, apologies for not getting the right solution the first time.
EDIT 2: I commented out the old WHILE clause and inserted the one that HABO suggested. Thanks again HABO.
source to share
I think this is what you are looking for:
DECLARE @UpdtAgt TABLE
(
ID INT,
Name VARCHAR(25)
)
INSERT @UpdtAgt
VALUES (1, 'Gandalf')
,(2, 'Hank')
,(3, 'Icarus')
UPDATE t
SET t.Assignment = a.Name
FROM TestUpdate AS t
INNER JOIN @UpdtAgt AS a
ON t.ShtID = a.ID
This should do all 4 lines at once.
PS ..
If you create tables like you did in your original post in the future, try keeping your column and variable names according to their purpose!
In your example, you used ID
, AgtID
and ShtID
and (most confusingly) TblID
(and I think they are all the same? [Please correct me if I'm wrong!]). If you called it AgtID
everywhere (and @AgtID
for a variable [No real need for @RowID
]), then it would be much easier to see what's going on! The same thing happens with Assignment
and Name
.
source to share
Because this is your first attempt at something like this, I want to congratulate you on something that works. Although not perfect (and what is it?), It meets the main goal: it works. There is a better way to do this using something known as a cursor. I remind you of the correct syntax using the following Microsoft page: Click here for complete instruction on cursors
Having said that, the code at the end of this post shows my quick solution to your situation. Please note the following:
- The table is
@TestUpdate
defined so that the query will run in MSSQL without using persistent tables. - Only the table
@UpdtAgt
needs to be configured as a temporary table. However, if this is used regularly, it would be better to make it a permanent table. - Operators
CLOSE
andDEALLOCATE
at the end it is IMPORTANT - forgetting about them will have rather unpleasant consequences.
DECLARE @TestUpdate TABLE (ShtID int, Assignment varchar(25))
INSERT INTO @TestUpdate
VALUES (1,'Fred')
,(2,'Barney')
,(3,'Fred')
,(4,'Wilma')
,(5,'Betty'),(6,'Leopold'),(7,'Frank'),(8,'Fred')
DECLARE @UpdtAgt TABLE (ID INT, Name varchar(25))
INSERT INTO @UpdtAgt
VALUES (1, 'Gandalf')
,(2,'Hank')
,(3,'Icarus')
DECLARE @recid int
DECLARE @AgtID int SET @AgtID=0
DECLARE @MaxID int SET @MaxID = (SELECT COUNT(*) FROM @UpdtAgt)
DECLARE assignment_cursor CURSOR
FOR SELECT ShtID FROM @TestUpdate
OPEN assignment_cursor
FETCH NEXT FROM assignment_cursor
INTO @recid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AgtID = @AgtID + 1
IF @AgtID > @MaxID SET @AgtID = 1
UPDATE @TestUpdate
SET Assignment = (SELECT TOP 1 Name FROM @UpdtAgt WHERE ID=@AgtID)
FROM @TestUpdate TU
WHERE ShtID=@recid
FETCH NEXT FROM assignment_cursor INTO @recid
END
CLOSE assignment_cursor
DEALLOCATE assignment_cursor
SELECT * FROM @TestUpdate
source to share