Need Opinions on Updating Large tables

I have an incomming table that is similar to the following...
locationNbr, SalesDate, SalesAmt, TranCode, SalesQty
I have to update a table that is formated as such...
locationNbr, historyType, Year, D001, D002, D003, D004... --> D365
I am running a join from the incoming table to the table I need to update to
determine what records I need to Insert.
Then I am using a VB app to create a batch of update statements (because I
couldnt figure out how to dynamically specify columns in T-sql) for those
whose key records already exist.
I would imagine this would run alot faster if I could figure out a way to do
this within an sproc.
Is there a good way that I can dynamically build a SQL Update statement
within an sproc?
And, as I am not extremely familiar with MSSQL, is there any other fast
update methods when you are updating large tables?
Thanks for any input.Jace wrote:
> I have an incomming table that is similar to the following...
> locationNbr, SalesDate, SalesAmt, TranCode, SalesQty
> I have to update a table that is formated as such...
> locationNbr, historyType, Year, D001, D002, D003, D004... --> D365
>
You can use dynamic SQL, but I'm not sure it's going to provide any
improvements over all the processing your going to have to do to move
that nicely normalized table into one that looks strangely denormalized.
What's the story behind the denormalized table? What about leap years?
They have 366 days.
Check out sp_executesql in BOL for more information about how to build
dynamic SQL statements from in T-SQL.
David Gugick
Imceda Software
www.imceda.com|||Well, actually it has 371 columns, but it only uses the last 7 col every 9
years. The reason for the denorm format is for speed in accessing the data
for a forecasting method. This system forecasts 100'000+ skus in several
thousand locations, and I am told that using this table vs a nomalized table
increases the speed a significant amount.
Thanks for your input. And um... What is BOL ? :)
"David Gugick" wrote:

> Jace wrote:
> You can use dynamic SQL, but I'm not sure it's going to provide any
> improvements over all the processing your going to have to do to move
> that nicely normalized table into one that looks strangely denormalized.
> What's the story behind the denormalized table? What about leap years?
> They have 366 days.
> Check out sp_executesql in BOL for more information about how to build
> dynamic SQL statements from in T-SQL.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||BOL is Books Online - the help documentation that comes with SQL Server.
The denormalized table design is potty and will not scale. Analysis Services
and indexed views are tools designed for this kind of work and can routinely
handle 100s of millions of rows of data.
David Portas
SQL Server MVP
--|||Jace wrote:
> Well, actually it has 371 columns, but it only uses the last 7 col
> every 9 years. The reason for the denorm format is for speed in
> accessing the data for a forecasting method. This system forecasts
> 100'000+ skus in several thousand locations, and I am told that using
> this table vs a nomalized table increases the speed a significant
> amount.
> Thanks for your input. And um... What is BOL ? :)
>
David is right. While denormailzed tables have been used for years in
read-only formats for reporting, it makes sense here to test a properly
designed, nomalized solution before commiting to this one.
David Gugick
Imceda Software
www.imceda.com|||Thanks for the input. Im going to do some testing on this in a denorm table
.
It appears the row count will be around 6.5 billion. Do you think that I
may run into trouble with that many records? Or will I have to split
different history types into different tables?
"David Portas" wrote:

> BOL is Books Online - the help documentation that comes with SQL Server.
> The denormalized table design is potty and will not scale. Analysis Servic
es
> and indexed views are tools designed for this kind of work and can routine
ly
> handle 100s of millions of rows of data.
> --
> David Portas
> SQL Server MVP
> --
>|||You'll almost certainly want to look at Analysis Services and Partitioned
Views for this.
Billions of rows are not uncommon - with the right hardware there is no
problem in principle with tables of that size. You should probably consult o
r
hire someone with AS and VLDB experience though.
David Portas
SQL Server MVP
--