can I update based on a join result with a calculation

My table design is
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
and I also have data files of the same structure. My goal is to import the
data files in order to update the Total table where the hKey matches.
I could
A. Read each record (4.5 million) and send to a stored procedure which does
a look up and add to the total field.
or
B. Import the data file into a temp table, join the HashKeyTotals table with
some sort of calc'd field summing the two totals and put that into a temp
table to be loaded back into the HashKeyTotals table.
I'm not even sure if B is possible but just a crazy idea. Will it work and
will it be that much faster than plan A? The HashKeyTotals table can have up
to 25 million records and I'd be loading a data file with 5 million records
in it each day.
Any thoughts?
ThanksSo, you're incrementing the Total by what you get in the import table for
the corresponding key? If so, try:
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [PK_HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [HashKeyTotals2] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [PK_HashKeyTotals2] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
insert HashKeyTotals values (1, 10)
insert HashKeyTotals values (2, 20)
insert HashKeyTotals2 values (1, 5)
insert HashKeyTotals2 values (2, 15)
go
update h
set
Total = h.Total + h2.Total
from
HashKeyTotals h
join HashKeyTotals2 h2 on h2.hKey = h.hKey
go
select * from HashKeyTotals
go
drop table HashKeyTotals, HashKeyTotals2
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Mike" <Mike@.mike.com> wrote in message
news:uuIuedUcGHA.3936@.TK2MSFTNGP05.phx.gbl...
My table design is
CREATE TABLE [HashKeyTotals] (
[hKey] [bigint] NOT NULL ,
[Total] [int] NULL ,
CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
(
[hKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
and I also have data files of the same structure. My goal is to import the
data files in order to update the Total table where the hKey matches.
I could
A. Read each record (4.5 million) and send to a stored procedure which does
a look up and add to the total field.
or
B. Import the data file into a temp table, join the HashKeyTotals table with
some sort of calc'd field summing the two totals and put that into a temp
table to be loaded back into the HashKeyTotals table.
I'm not even sure if B is possible but just a crazy idea. Will it work and
will it be that much faster than plan A? The HashKeyTotals table can have up
to 25 million records and I'd be loading a data file with 5 million records
in it each day.
Any thoughts?
Thanks|||Yes thats exactly what I need. Thank you very much.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23LBk8iVcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> So, you're incrementing the Total by what you get in the import table for
> the corresponding key? If so, try:
> CREATE TABLE [HashKeyTotals] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [PK_HashKeyTotals] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [HashKeyTotals2] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [PK_HashKeyTotals2] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> insert HashKeyTotals values (1, 10)
> insert HashKeyTotals values (2, 20)
> insert HashKeyTotals2 values (1, 5)
> insert HashKeyTotals2 values (2, 15)
> go
> update h
> set
> Total = h.Total + h2.Total
> from
> HashKeyTotals h
> join HashKeyTotals2 h2 on h2.hKey = h.hKey
> go
> select * from HashKeyTotals
> go
> drop table HashKeyTotals, HashKeyTotals2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Mike" <Mike@.mike.com> wrote in message
> news:uuIuedUcGHA.3936@.TK2MSFTNGP05.phx.gbl...
> My table design is
> CREATE TABLE [HashKeyTotals] (
> [hKey] [bigint] NOT NULL ,
> [Total] [int] NULL ,
> CONSTRAINT [HashKeyTotals] PRIMARY KEY CLUSTERED
> (
> [hKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> and I also have data files of the same structure. My goal is to import the
> data files in order to update the Total table where the hKey matches.
> I could
> A. Read each record (4.5 million) and send to a stored procedure which
> does
> a look up and add to the total field.
> or
> B. Import the data file into a temp table, join the HashKeyTotals table
> with
> some sort of calc'd field summing the two totals and put that into a temp
> table to be loaded back into the HashKeyTotals table.
> I'm not even sure if B is possible but just a crazy idea. Will it work and
> will it be that much faster than plan A? The HashKeyTotals table can have
> up
> to 25 million records and I'd be loading a data file with 5 million
> records
> in it each day.
> Any thoughts?
> Thanks
>

can i make one query out of these two?

i have two views. the first finds related images for products, but only if the image is the primary image. here it is:

CREATE VIEW images AS
SELECT
p.product_id,
pI.productImage_title as productImage_title
FROM
dbo.products p INNER JOIN
dbo.productImages pI ON p.product_id = pI.product_id
WHERE pI.productImage_primary = 1

the second view gets all the products, and uses the first view to find an image, if there is one:

SELECT
p.product_id as product_id,
p.product_partNumber as part_number,
p.product_name as product_name,
i.productImage_title as small_image,
FROM
dbo.products p LEFT OUTER JOIN
dbo.images i ON p.product_id = i.product_id

i would like to be able to do this with just one view. i'm not sure how to do it though while still limiting the images to only primary ones.

thanks for any help :)If this in Oracle, you could use inline views as follows :

SELECT
p.product_id as product_id,
p.product_partNumber as part_number,
p.product_name as product_name,
i.productImage_title as small_image,
FROM
dbo.products p,
(SELECT p.product_id,
pI.productImage_title as productImage_title
FROM dbo.products p INNER JOIN
dbo.productImages pI ON p.product_id = pI.product_id
WHERE pI.productImage_primary = 1) i
WHERE p.product_id = i.product_id(+)

Originally posted by kfenstad
i have two views. the first finds related images for products, but only if the image is the primary image. here it is:

CREATE VIEW images AS
SELECT
p.product_id,
pI.productImage_title as productImage_title
FROM
dbo.products p INNER JOIN
dbo.productImages pI ON p.product_id = pI.product_id
WHERE pI.productImage_primary = 1

the second view gets all the products, and uses the first view to find an image, if there is one:

SELECT
p.product_id as product_id,
p.product_partNumber as part_number,
p.product_name as product_name,
i.productImage_title as small_image,
FROM
dbo.products p LEFT OUTER JOIN
dbo.images i ON p.product_id = i.product_id

i would like to be able to do this with just one view. i'm not sure how to do it though while still limiting the images to only primary ones.

thanks for any help :)|||i am working in SQL server.|||Sorry, I cant help you with exact syntax but SQL Server also supports inline views -- dont know which version. May be, you can look into the documentation.

Originally posted by kfenstad
i am working in SQL server.