SQL Server Performance Forum – Threads Archive

How to without a cursor ?

Hi There I have a table:
Table A
========
Column 1
Column 2
Column 3 Another
Table B
========
Column 1
Column 2
Column 3
Column 4 Simplifying the situation alot, I need to insert the rows from table A into table B, however each new row int table 4 must have an incremental value starting from 0 upwards inserted into Column 4 in table B. Currently i use i cursor thorugh Table A with a incremental variable in the cursor loop, how can this be done without a cursor ? I would like to use one INSERT statement to accomplish this, but since nothing like ROWNUM is available is sql server 2000 it is not that easy ? Thanx

Create a temp holding table with columns 1, 2 and 3, and an int identity(1,1) column. Insert the data from A, with an ORDER BY clause, into the temp table, then insert from the temp table into B.

Why do you need this? Madhivanan Failing to plan is Planning to fail

Looks like inserting data into a child table, with a "subidentifier" column to allow multiple entries for the same parent key.

Hi Adriaan I do , do this in alot of places where i can.
Basically this is a 2000 line stored procedure.
I am just concerned about overhead, personally i am not sure if a huge stoed procedure would be better with 100 cursors or 100 temp tables. Especially since this sp is fired by webservices so at any given time many could be executed at the same time. What do you guys think, less cursors and more temp tables, or vica versa?

Hi Adriaan ALos i should mention, the places that i dont do it, is because i am confined by xsd definition restraints , therefore if i add an identity column the actual xml definition changes which is not acceptable. I just want to make 100% besides temp tables there is no other way to do with, with a function or something i am not aware of ? Thanx again

In that case, use table variables instead of temp tables. There will still be a point where SQL decides that the table variable requires a temp table, but you have a better chance of reducing the overhead. Rule #1, always: avoid cursors whenever possible.

Just noticed your post mentioning an XSD. You have my sympathies.[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]

Thanx Adriaan Yes i am aware of Rule No1 <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> But sometimes i dont see how not to use a cursor , or in the case of this sp very often !!! Becuase what do you do when each rows for a table needs to be evaluated and different logic applied. For example if i have the same table A as above, i have to check that each row does not already exist in Table B, if it do i need to insert only that 1 row for Table A somehwere else say Table C, if is does i need to validate each value in the column to something and perhaps insert into table A or anotehr Table E. If anyone can tell me how to do that with a cursor row by row , i will be imrpessed.

You are aware of the EXISTS clause? I must say it sounds more and more like a staging table might be the thing, where you can have an additional column where you set where each row will go, plus a column for the SPID so you know which rows are for the current connection. Clean up before and after yourself (only one connection can have the same SPID).

In your example you can avoid cursor by Insert into tableC(columns)
Select * from tableA A
where not exists(select * from tableB where uniquecol=A.uniquecol) Madhivanan Failing to plan is Planning to fail

Hi Guys Yes i am aware of all these methods, i guess you would have to see the whole sp to understand, the complexity of the logic around the cursor is what really makes it difficult to use a single INSERT as suggested by Madhivanan. Yes the data is already in staging tables with a guid to uniquely identify the relevant rows. That is just where it all starts to get complex. But thanx for the suggestions, there maybe one cursor i can replace with the suggestions here. Thanx Again

]]>