Looping over records and changing values

Question

Post reply

Looping over records and changing values

Ben-347858

Old Hand

Points: 391
More actions
January 24, 2007 at 7:13 am

#118631

I'm trying to figure out a way to write some SQL to handle updating existing records. I'm hoping someone can steer me in the right direction as I'm a little stumped.
Here is my scenario:
I have a DTS Package that runs and imports records from several text files into some temp tables. I then execute a stored proc to take records from this temp table and insert them into 2 tables.
What I need to do is compare each record in the temp tables to what's currently in the real tables. If I have a record that is an exact match between the temp table and the real table I need to update a field called endDate to be one day before the effDate in the record in the temp table.
Right now the stored proc does an insert based on a select statement. I started to re-write it to use a cursor so I can loop over each record but I'm not sure how to effectively compare the records.
Anyone have any ideas?
Thanks,
Ben

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 1

How big is the table you are comparing?

checking row by row would be time consuming....

I would just do it all in the DTS package.

1. Insert tnto the temp tables

2. Compare table data

3. run update statements

4. Insert into table

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 2

update table1

set date = newdate

from temp b where table1.somevalue = b.somevalue and table1.someotherval = b.someotherval

or

update table

set date = newdate

where exists (select * from temp b where table1.somevalue = b.somevalue and table1.someotherval = b.someotherval)

Tom

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Can you also specify how many columns are required to be compared. Cursor is very expensive method with respect to Performance.

Prasad Bhogadi
www.inforaise.com

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

Heh... and I wouldn't do any of it in DTS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ben-347858 Old Hand Points: 391 More actions · Answer 5

it would probably be about 4 or 5 columns. I'm really don't want to have to use a cursor if I don't have to. Running some tests using a sp with a cursor versus running one without using a cursor shows some stunning results.

With the cursor importing one set of files took about 45 minutes. Running that same set without the cursor took about 30 seconds.

I'll try some of the solutions suggested but I'm not sure they will work or not.

Thanks,

Ben

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 6

Using joins, create an "UPSERT"...

UPDATE where there's a match... INSERT where there isn't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)