sim.plified.com

Chris Pollock - web developer (PHP/mySQL & ASP.NET)
undivided… my thoughts on world, family, church, business, technology and Jesus Christ (all in all)

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

Technorati Tags: T-SQL,SELECT,LOOP

5 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