Cursor or multiple update statements (or something else?!)

Question

Post reply

Cursor or multiple update statements (or something else?!)

sclements

SSC-Addicted

Points: 495
More actions
February 23, 2012 at 2:40 am

#391629

Hi,
I'm needing to update around 1.2 million records in our database and am using a script to do the update below:
declare @postcode nvarchar(50)
set @postcode = (select top(1) postcode from address where objectid = 'xxxx')
Update Applicants set locationid = (select dbo.getlocationid(@postcode)) where applicantid = 'xxxx';
The function dbo.getlocationid looks up a locationid based on a postcode.
I've got all the records I need to update in a spreadsheet, so is it more efficient to use a cursor in this case, or simply to copy/paste several hundred thousand lines of code and run directly?
I am fairly new to cursors (or suitable alternatives!) so would appreciate help here. Thanks

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

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 1

Try something like this. It will run much faster than (many) individual updates.

UPDATE m

SET field_to_update = MyValue

FROM MyTable m

INNER JOIN (

SELECT '1' AS MyKey, '2' As MyValue

UNION ALL SELECT '2', '3'

UNION ALL SELECT '3', '4'

UNION ALL SELECT '4', '5'

UNION ALL SELECT '5', '6'

UNION ALL SELECT '6', '7'

UNION ALL SELECT '7', '8'

UNION ALL SELECT '8', '9'

) x

ON m.MyKey = x.MyKey

You want to build the SELECT/UNION ALL statements with a formula in Excel. I have a spreadsheet that does just that but I normally use it only for bulk inserts.

If there are too many rows, you may want to break it up into 3 or 4 (or more) batches.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

sclements SSC-Addicted Points: 495 More actions · Answer 2

Thanks for that - I'll take a look at this and see how I get on!

Could you share the spreadsheet with me anyway, so I can take a look at the structure of it?

Ta

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

I probably can't because I developed it for use at my company so I'd consider it proprietary.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

stephensjwu SSC Journeyman Points: 80 More actions · Answer 4

Hi,

Try the following script to load csv file into SQL server.

Create table with the the fields to match up with the CSV file.

Update rowterminator or fieldterminator accordingly based on the csv file

DECLARE @script varchar(1000)

SET @script = 'BULK INSERT [DB].dbo.[TableName] FROM

''C:Sample.csv''

WITH (ROWTERMINATOR = '''+CHAR(10)+''', FIELDTERMINATOR = ''|'')'

EXEC(@script)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

scelements asked:

Could you share the spreadsheet with me anyway, so I can take a look at the structure of it?

And I responded:

I probably can't because I developed it for use at my company so I'd consider it proprietary.

Here's a snapshot of what you need in your spreadsheet. Note that the original query I suggested shows in cells A1:A8, the data is in B12:B19 and the results are in A12:A19 (they match the original query). The formulas to calculate the results show in E12:E13, and E13 can simply be copied down to get the rest of the UNION ALL SELECT statements.

The spreadsheet I've developed is generic in the sense that you define your table/columns in one sheet, load the data into another and then it produces the bulk INSERT that you can use in a format like this. It support MS SQL Server and Oracle databases (working on MySQL). It currently supports 1000 rows and 25 columns (but this can be expanded).

Wish I could share it with you but I can't for the reason I stated above.

I'd be delighted to hear if any of this worked for you.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

CELKO (2/24/2012)
CREATE VIEW Applicant_Locations
AS
SELECT A.*, L.location_id
FROM Applicants AS A, Locations AS L
WHERE A.postal_code = L.postal_code;

BWAAA-HAAA!!!!! Joe, you really should read the ANSI Standards so that Celko fellow doesn't jump all over you for not knowing anything about them. 😉

--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)

sclements SSC-Addicted Points: 495 More actions · Answer 7

CELKO (2/24/2012)
DECLARE @local_postal_code NVARCHAR(50);
No postal code on earth is fifty characters long; 12 is the worst I know. No postal code on earth uses Unicode. By international agreement, they are Latin-1, alphanumerics which have to be available in all Unicode language sets. If you invite garbage, you will get it.

The problem is I'm working with a database which has junk data on it, so I'm having to process the rubbish as well.

CELKO (2/24/2012)
Why are you putting this location_id PHYSICALLY in the Applicants tables? oh, you think a row is a record and cannot think of any other way.

Because the system that uses this data is looking for it in the locationid of the applicants table - unfortunately I didn't build the system, so I'm having to work with what I have!