x

Update Statement in SSIS

Hi,

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

aRookieBIdev gravatar image

aRookieBIdev
2.3k 53 57 62

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

Valentino Vranken gravatar image

Valentino Vranken
1.5k 1 2 7

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x946
x133
x52

asked: Sep 24, 2012 at 07:26 AM

Seen: 1044 times

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