Mar 28, 2008
T-SQL Loop through SELECT Statement
I recently changed the a data structure in calendar program and needed to move some data around. Normally to do this I would write an external app to query the database and rewrite the data. This time I decided to try using T-SQL. It worked great. Below is my code:
DECLARE @Event_ID int DECLARE my_cursor CURSOR FOR SELECT Event_ID FROM ITS_CALENDAR_EVENTS_LIST WHERE EVENT_SA_Flag = 1 OPEN my_cursor FETCH NEXT FROM my_cursor INTO @Event_ID WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO ITS_CALENDAR_EVENTS_TO_FLAGS (Event_ID, Event_Flag_ID) VALUES (@Event_ID, 1) FETCH NEXT FROM my_cursor INTO @Event_ID END CLOSE my_cursor DEALLOCATE my_cursor
9 Comments, Comment or Ping
sulfur_scratch
jesus christ….
Aug 15th, 2008
Marqs
First thing first, Loops in tSQL are evil performance muncher.
Most of the times you can think a step further and design a bulk insert/update/wathever that is way faster and cheaper in the CPU and disk.
In your case this is done fairly easy. in fack you already do it in your first select statement for the loop
"SELECT Event_ID FROM ITS_CALENDAR_EVENTS_LIST
WHERE EVENT_SA_Flag = 1″
all you have to do is to put the
"INSERT INTO ITS_CALENDAR_EVENTS_TO_FLAGS (Event_ID, Event_Flag_ID)" statement above it and include the "Event_Flag_ID" bit-thingy in the select.
then you end up with
INSERT INTO ITS_CALENDAR_EVENTS_TO_FLAGS (Event_ID, Event_Flag_ID)
SELECT Event_ID,1 FROM ITS_CALENDAR_EVENTS_LIST
WHERE EVENT_SA_Flag = 1
Aug 29th, 2008
Marqs
Perhaps in your case the seed won't be much improved.
but trust med when tables grow above 3-4k rows you defenetly do not want to loop over them
Aug 29th, 2008
CLAeMYR_1ne
yah, the insert in select way i far better for his requirement..
but what he did is what i really needed since i need to get the ordinal(sequence of the select result) and insert it to the other table..
if ever you might have a remedy for this by just plain insert into select that would be a great help.
Thnx.
Aug 29th, 2008
Alex
Yikes.
Aug 12th, 2009
Abdul Rauf
Very much informative and helpful.
Mar 12th, 2010
Yogesh Shah
Ecellent example and helped me out.
May 3rd, 2010
Website
Thanks a lot! MARQS method worked for me.
Mar 21st, 2011
ESalas
In my case I have 3.7 million rows in a single table that contains the key information (Specifically 4 fields) that are necessary for the update statement that will be executed against 1 of 3 possible tables. Below is the code I am executing (found on this site) and the execution is taking upwards of 35 minutes. I normally handle all of my business logic in the middle tier but am really looking to up my skills in TSQL. Any thing you can do to help I would appreciate. Thanks Guys
ALTER PROCEDURE [dbo].[UpdateAssessmentDetail]
AS
BEGIN
DECLARE @assess_id bigint
DECLARE @class_id bigint
DECLARE @student_id bigint
DECLARE @question_id bigint
DECLARE @question_answer bit
DECLARE @AssessmentType varchar(30)
DECLARE @AssessmentTable varchar(50)
DECLARE @SQL varchar(2000)
DECLARE my_cursor CURSOR FOR
SELECT assess_id, class_id, student_id, question_id, question_answer
FROM _Student_Assessment_Dtl
ORDER BY _Student_Assessment_Dtl.assess_id
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @assess_id, @class_id, @student_id, @question_id, @question_answer
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AssessmentType = (SELECT table_suffix from dbo.assessment where assess_id = @assess_id)
SET @AssessmentTable = 'Student_Assessment_Dtl' + @AssessmentType
SELECT @SQL = 'UPDATE [' + @AssessmentTable + ']
Set question_answer=' + convert(varchar(10), @question_answer)
+' WHERE assess_id=' + convert(varchar(10), @assess_id)
+ ' AND class_id='+ convert(varchar(10), @class_id)
+ ' AND student_id='+ convert(varchar(10), @student_id)
+ ' AND question_id='+ convert(varchar(10), @question_id)
EXEC (@SQL)
FETCH NEXT FROM my_cursor
INTO @assess_id, @class_id, @student_id, @question_id, @question_answer
END
CLOSE my_cursor
DEALLOCATE my_cursor
END
Jan 24th, 2012
Reply to “T-SQL Loop through SELECT Statement”