Update Statement in SSIS


I have a scenario where i am moving 40 million records in to a table and then run a update script to update some records with the aggregate values example total count for each countries. Since i cant calculate these values in run time.(not the total of 40 million)

What is the best alternative solution rather than updating 40 million records.

Thanks in advance, Kannan

more ▼

asked Sep 24, 2012 at 07:26 AM in Default

avatar image

2.8k 56 65 71

Does the update evaluate the entire 40 million rows or you what have a WHERE statement?

Sep 24, 2012 at 07:32 AM sp_lock

can you give us a description of the calculations that you are needing to apply so we can make suggestions?

Sep 24, 2012 at 08:13 AM Fatherjack ♦♦

i want to calculate the total value of a coulmn based on the country . example total for US , total for europe etc.

Sep 24, 2012 at 08:16 AM aRookieBIdev
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If you're calculating totals, why would you want to store them in the same table as the details?

To avoid updating those 40 million records, you could store the totals in a separate table, something like CountryTotal. You'd need two fields: CountryID and Total. Or better, three fields, add a primary key CountryTotalID as well.

This assumes your details table already has a CountryID. If that's not the case, you may want to consider remodeling a bit by creating a Country table that contains the list of countries. Your details table would then point to this new table.

more ▼

answered Oct 05, 2012 at 12:47 PM

avatar image

Valentino Vranken
1.5k 2 4 12

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 24, 2012 at 07:26 AM

Seen: 1244 times

Last Updated: Oct 05, 2012 at 12:47 PM

Copyright 2018 Redgate Software. Privacy Policy