0

struggling with this one, quite a lengthy description so ill explain best I can:

I have a table with 12 columns in, 1 being a primary key with identity_insert, 1 a foreign key , the other 10 being cost values, ive created a statement to group them into 5 categories, shown below:

select
(ProductID)ProjectID,
sum(Cost1)Catagory1,
sum(Cost2)Catagory2,
sum(Cost3 + Cost4 + Cost5 + Cost6 + Cost7) Catagory3,
sum(Cost 8 + Cost 9)Catagory4,
sum(Cost10)Catagory5
from ProductTable group by ProductID

ive changed the names of the data to make it more generic, they aren't actually called Cost1 etc by the way ;)

the foreign key can appear multiple times (ProductID) so in the above query the related fields are calculated together based upon this... Now what ive been trying to do is put this query into a table, which i have done successfully, and then update the data via a procedure. the problem im having is that all the data in the table is overwritten by row 1 and when theres is thousands of rows this is a problem.

I have also tried putting the above query into a view and the same result... any suggestions would be great :)

update NewTable set
ProductID = (ProductView.ProductID ),
Catagory1 = (ProductView.Catagory1 ),
Catagory2 = (ProductView.Catagory2 ),
Catagory3 = (ProductView.Catagory3 ),
Catagory4 = (ProductView.Catagory4 ),
Catagory5 = (ProductView.Catagory5 )
from ProductView

I need something along the lines like above.... but one that doesn't overwrite everything with row 1 haha ;)

ANSWERED BY: Noman_1

create procedure NewProducts
insert into NewTable
select ProductID.ProductTable,
Catagory1.ProductView,
Catagory2.ProductView,
Catagory3.ProductView,
Catagory4.ProductView,
Catagory5.ProductView
from ProductView
inner join ProductTable on ProductView.ProductID = ProductTable.ProductID 
where not exists(select 1 from NewTable where ProductView.ProductID = NewTable.ProductID)

above procedure locates the new Product that has been created within a view, the procedure query detects that there is a Product that is not located in the NewTable and inserts it via the procedure

Crezzer7
  • 2,265
  • 6
  • 32
  • 63

2 Answers2

2

As far as i know, and since you want to update all the products in the table, and each product uses all the sums of the product itself from origin, you actually need to update each row 1 by 1, and as consecuence when you do an update like the next, its your only main way

update newtable
set category1 = (select sum(cost1) from productTable where productTable.productId = newtable.ProductID),
category2 = (select sum(cost2) from productTable where productTable.productId = newtable.ProductID),
etc..

Keep in mind that if you have new products, they wont get inserted with the update, you would need like this in order to add them:

Insert into newtable
Select VALUES from productTable a where productId not exists(select 1 from newTable b where a.ProductId = b.ProductId);

A second way, and since you want allways to update all the data, is to simply truncate and do a insert select right after.

Maybe on an Oracle, you would be albe to use a MERGE but im unaware if it would really improve anything.

I asume that simply having a view would not work due the amount of data you state you have.

EDIT, I never knew that the MERGE STATMENT is actually avaiable on SQL Server 2008 and above, with this single statment you could do an UPDATE/INSERT on all but it's efficiency is unknown to me, you may want to test it with your high amount of data:

MERGE newtable AS TARGET
USING select ProductId, sum(cost1) cat1, sum(cost2) cat2 ... 
FROM productTable Group by ProductId AS SOURCE
ON TARGET.ProductId = SOURCE.ProductID
WHEN MATCHED 
THEN UPDATE SET TARGET.category1 = cat1, TARGET.category2 = cat2...
WHEN NOT MATCHED 
THEN INSERT (ProductId, category1, category2,...) 
VALUES (SOURCe.ProductId, SOURCE.cat1, SOURCE.cat2...);

More info about merge here: http://msdn.microsoft.com/library/bb510625.aspx

The example at the end may give you a good overview of the sintax

Noman_1
  • 473
  • 1
  • 6
  • 17
  • im using Visual Studio 2013 Lightswitch and for some reason the view wont add in my datasource, it cant be found other wise this would be so simple... trying to implement your method now. will i need joins in each select statement? – Crezzer7 Jul 29 '14 at 12:07
  • I asume you refer to Truncate -> insert. Keep in mind that if you need the data while the procedure is working, you may find the table is locked or has no data – Noman_1 Jul 30 '14 at 08:20
0

You haven't given any join condition. SQL Server cannot know that you meant to update rows matched by productid.

update NewTable set
ProductID = (ProductView.ProductID ),
Catagory1 = (ProductView.Catagory1 ),
Catagory2 = (ProductView.Catagory2 ),
Catagory3 = (ProductView.Catagory3 ),
Catagory4 = (ProductView.Catagory4 ),
Catagory5 = (ProductView.Catagory5 )
from NewTable
join ProductView pv on NewTable.productid = pv.productid

You don't need a view. Just past the view query to the place where I said ProductView. Of course, you can use a view.

usr
  • 168,620
  • 35
  • 240
  • 369
  • it still overwrites everything in the table with row 1 – Crezzer7 Jul 29 '14 at 12:17
  • Is the output of your view correct? Does the target table contain what you think it contains? Post the actual execution plan of this query. – usr Jul 29 '14 at 12:20
  • Again, post the execution plan. – usr Jul 29 '14 at 12:25
  • only 12 months into sql and not sure how I get that sorry :/ – Crezzer7 Jul 29 '14 at 12:31
  • 1
    http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan Don't just ignore what I say if you don't understand it. Ask and search. – usr Jul 29 '14 at 12:32
  • There is no group by here, this way you dont get the sum but use a lot of rows to update a single one – Noman_1 Jul 30 '14 at 08:38