Best Way To Update Existing Rows

IUpdating existing rows in a SQL server 2005 destination table

--I have a CSV file and every row needs to perform an update on a production table

The most straight forward is to use an OLEDB Command Data Flow Transformation Object that sends one SQL statement per row coming in. How do I configure this object ?

That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. Can you please tell me how I can set up the Execute SQL Task to do set based updates in the Control flow?

thanks in advance

Dave

Mr Pro Tools wrote:

IUpdating existing rows in a SQL server 2005 destination table

--I have a CSV file and every row needs to perform an update on a production table

The most straight forward is to use an OLEDB Command Data Flow Transformation Object that sends one SQL statement per row coming in. How do I configure this object ?

I assume you can write SQL, right?

All explained here: http://msdn2.microsoft.com/en-us/library/ms141138.aspx

Mr Pro Tools wrote:

That might be okay if you've only got a small number of updates, however if you've got a large set, I prefer landing this set to a temporary table and then doing Set based updates in a following Execute SQL task. Can you please tell me how I can set up the Execute SQL Task to do set based updates in the Control flow?

thanks in advance

Dave

Again, I assume you can write SQL. Just paste your SQL into the 'SQL Statement' proeprty of the Execute SQL Task.

-Jamie

|||

Thanks Jamie

First method works --Profiler is telling me its taking about a second per update

but is too slow as I have 900,000 records to update across two tables each having 7 million records which will take

889,641 records in the file supplied / 3600 per hour (1 per second)

= 250 hours

= 10 days

the Excecute SQL task looks like another option and sure I can paste into the SQL Statement window my sproc Exec dbo.MySproc ?,?

How do I map ?,? to each row in the Execute SQL Task--in other words what are the main properties I have to set for row by row processing and is it much faster than the OLE DB Command Transformation method --which is nice and simple and slow

thanks in advance

Dave

|||

Mr Pro Tools wrote:

Thanks Jamie

First method works --Profiler is telling me its taking about a second per update

but is too slow as I have 900,000 records to update across two tables each having 7 million records which will take

889,641 records in the file supplied / 3600 per hour (1 per second)

= 250 hours

= 10 days

the Excecute SQL task looks like another option and sure I can paste into the SQL Statement window my sproc Exec dbo.MySproc ?,?

How do I map ?,? to each row in the Execute SQL Task--in other words what are the main properties I have to set for row by row processing and is it much faster than the OLE DB Command Transformation method --which is nice and simple and slow

thanks in advance

Dave

I'm confused (seems to happen alot these days ) Why are you trying to map any columns at all using '?' ?

All you have to do is load the data into a temporary staging area (using OLE DB Destination) and then issue a SQL statement along the lines of:

UPDATE t

SET t.<col> = te.<col> ,......

FROM target t INNER JOIN temp te ON <some join columns>

Also, if OLE DB Command really is taking one second per row then your bottleneck is not the OLE DB Comamnd. its something else.

-Jamie

|||

Mr Pro Tools wrote:

How do I map ?,? to each row in the Execute SQL Task--in other words what are the main properties I have to set for row by row processing and is it much faster than the OLE DB Command Transformation method --which is nice and simple and slow

Dave,

The OLEDB command is just slow becasue performs the same command in a row by row basis. The intention of using a Execute SQL task is to be able to perform execute a SQL command over a set of rows at once. What Jamie is sugesting is to push the rows in your CSV file to an staging table and then, in control flow to issue un Update stament against your target table using the staging table rows. That would be a one time update.

|||

UPDATE t

SET t.<col> = te.<col> ,......

FROM target t INNER JOIN temp te ON <some join columns>

Yeah that is simple enough what Im getting at is can the Execute SQL Task do row by row processing like the DTS DDQ task and how do set the properties if so

In my case the updates will only occur if one of columns in the temp staging table has an particular value in a column

If it has --we do the update otherwise do a delete

thats why I had ? as parameters for a store prod

|||

No, the Execute SQL Task does not do row-by-row processing. As already established, that's what the OLE DB Command does.

-Jamie