May 29, 2009 at 8:06 am
Hello,
In my current development I need to perform a multi update statement on SQL Server 2005.
To make it easy here is what I have:
An incoming csv file with 2 fields, once splitted, I look in SQL table all fields matching this 2 fields and set a third column to true/false (in the same table)
To do that I build dynamicaly a String like that
-"UPDATE SET .... WHERE [col1] = @field1 AND [col2] = @field2; UPDATE SET ...; UPDATE ....; UPDATE ..."
I don't like this solution ... Its not clear, and easy to maintain, performance also is going to be really poor.
I don't think this is possible to do that through Stored Procedure.
Whats the best practice to avoid having a very long String with a lot of UPDATE ?
csv files contains 2,000 or 20,000 or even 200,000 records ... on a 10 millions records table
Thank you for the help 🙂
May 29, 2009 at 8:55 am
I'd dump the CSV data into a table then do a set-based update. Something like:
UPDATE TABLEA
SET boolean_column = TRUE
WHERE
EXISTS (SELECT 1 FROM csv_table WHERE TableA.columnA = csv_table.columnA AND TableA.columnB = csv_table.columnB)
If you were to post some table structures and sample data I could be more specific.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2009 at 9:03 am
Thank you for the answer.
What you suggest is what I'm doing today, I would like to forbid this kind of method.
Do you have another solution to so the same without sending a huge Sql ?
I want also to forbid the solution to submit a generated sql file and execute it remotely.
As I dont know all features from SQL Server, I'm sure this type of problem have a solution.
Thank you
May 29, 2009 at 9:07 am
Why would you forbid a solution like Jack's? It's very standard, performs well, and is easy to build and maintain.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 29, 2009 at 9:09 am
No, this is not what you are doing today. This does your update in one statement, not by building a string of sql statements and then executing the string.
If you were to post some table structures , current code, and sample data I could provide a more specific answer. See the top link in my signature line.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 29, 2009 at 10:57 am
Instead of the Where Exists, I'd probably do the whole thing as a join, either to an import table, or to the CSV using Openrowset, but it's the same concept. Would have to test both for performance.
Either way, it's going to be a lot better than the current solution. A LOT better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply