Sum of one vielf inserted into a different table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sum of one vielf inserted into a different table

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2011-02-16 : 00:51:19

Hi
Can anyone help with this issue, my data is as follows

Table = 1

Field1 Field2
a 2
a 3
b 7

If I use the following

select field1, (SUM(field2)) as total from support group by field1

I get

a = 5
b = 7

I now want to take these totals and populate them into a different table

Table 2 - Field 3

How is that possible?

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2011-02-16 : 01:03:46

Hi there, sorry I should be updating table 2 field 3

so in the example

Table 2

Field1 Field3
a 5
b 7

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-16 : 01:06:58

do you mean you want to insert the specified Same result set in two different tables, table1 and table2?

Insert into table1 (ColName,ColName)
select field1, (SUM(field2)) as total from support group by field1

Insert into table2 (ColName,ColName)
select field1, (SUM(field2)) as total from support group by field1

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2011-02-16 : 04:21:11

Hi there,

Thanks for the tips, so what I have done is the following

Create table

Create Table TableB (itemid varchar(55) null, F1 int null)

Now as this data is continuely updating I now have 3 scripts to get the tables to update, all I need to figure out now is to create either a trigger or stored procedure to update every 4 hours

step 1 - clean the tables out so I do not have any duplicates

delete from TableB where f1 like '%%'

step 2 - repopulate the holding table

Insert into tableb (itemid,f1)
select itemid, (SUM(number1)) as total from support group by itemid

step 3 - update the table to contain the info.

update itemssold
set user1 = (select f1 from TableB where tableb.itemid = itemssold.itemid)

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-16 : 05:26:12

if there is no updates in the old records of your original table then perhaps you should schedule the following

Insert into table1 (Col1Name,Col2Name)
select field1, (SUM(field2)) as total
from support
Left Join table1 on table1.col1Name=Support.Field1
Where table1.Col1Name is null
group by field1

Insert into table2 (Col1Name,Col2Name)
select field1, (SUM(field2)) as total
from support
Left Join table1 on table2.col1Name=Support.Field1
Where table2.Col1Name is null
group by field1

This will insert only newly added records

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources