Cursor required for update?

Hello All,

I am trying to determine the most efficient way of performing an update and am new to SQL Server. Here's what I am trying to do:

1. Get max of numerical address_ID field in Table 1.
2. Update records in Table 2 where homeadd_id is null with results from #1, plus 1 to increment.
3. Back to Table 1 to append a new record with newest homeadd_id number, plus fill in a couple of fields in that row.
4. Continue until all records from 2 are filled in.

I hope that this is clear enough - sorry, it's been a long time since I've coded anything and am just getting my feet wet.

Thanks for any advice.
CarmenYour approach doesnt seem correct.
As soon as you update everything to a new number that is null in table 2 it would be nothing more to update so it can be done only once.
The correct way would be insert record into table 1 with max(address_id) + 1 after record inserted go for inserting records into table 2 with new address_id.

Hope I understood your question correctly.

Good Luck.sql

Cursor or Temp table

I just want to know which one is more efficient cursor or temp table for looping through a record set?
I am basically looking for better performance and server utilization.I generally prefer set based solutions over cursors, so I'm more than 95% sure that I'd recommend the temp table solution, but I can't be sure about that without knowing more about the problem.

-PatP|||95%?

That low?|||For looping through a recordset i.e., record by record, you will need a cursor. Ofcourse, you can bring the data from many tables into a #temp table, and use a cursor on it, that is to say, the SELECT statement of the cursor could be on the #temp table.

It may affect the performance of the stored procedure, but it is negligible. It depends on what you want.

Roshmi Choudhury|||It partly depends upon what you plan on doing with the records. If you do a simple SELECT, then most-likely the DBMS is already creating a temporary-table to store the results. If you are requesting a live query-view for updating purposes, the SELECT is effectively a filter on the main table.

Your choice probably won't make any difference to the DBMS server; the impact, if any, will be upon the simultaneous users of the table. ("Large things" and "long-time things" are generally "bad things.")

My rule-of-thumb recommend is K.I.S.S. ... keep it simple. Choose the approach that makes the most sense to your application and let the DBMS take care of its own business. If in the future you can prove that it is a problem, then you can change it.|||I disagree, for looping thru a record set you most definitely do not need a cursor, using an IF WHILE loop is just as effective, and carries none of the problems associated with cursors.. Many shops - mine included - do not allow cursors on their servers...Seems like the only people who want to use a cursor are those people who can't write one that won't get hung and take a server down with it... Just my .02

Nick|||You don't ALLOW cursors? That's (unnecessarily) extreme.

If you truly need to "loop" through your dataset, then a cursor is an appropriate solution. The larger issue is WHETHER you need to loop through the dataset. I'm sure Pat would agree that "95%" of the time when somebody uses a cursor it's because they are not familiar enough with set-based solutions, and in these cases cursors are virtually always less efficient.

Post the problem you are trying to solve, and I'd bet that somebody here can show you how to solve it using set-base operations.