Insert using select - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Insert using select

Author

Topic

antvei
Starting Member

3 Posts

Posted - 2011-04-01 : 16:18:07

[code]
insert into CompletedTable (Resource_Name,Project_Name,Project_Actual, OT, OB, OQ, DRE, PIR, OverAll_Actual_OT, OverAll_Actual_OB, OverAll_Actual_OQ, OverAll_Actual_DRE, OverAll_Actual_PIR)
select b.Resource, a.Project, Cost_Actual, Case When Actual_Install_Date <= Projected_Install_Date then 1 else 0 end, case when Cost_Actual <= (Cost_Budget * 1.1) then 1 else 0 end,
OQ_Rating,DRE_Rating, PIR_Rating, SUM(Cost_Actual),SUM(Cost_Actual),SUM(Cost_Actual),SUM(Cost_Actual)
from PMV37000 a inner join PMV37010 b
on a.Project= b.Project
group by b.Resource, a.Project, a.Cost_Actual,a.Actual_Install_Date, a.Projected_Install_Date, a.Cost_Budget, a.OQ_Rating, a.DRE_Rating, a.PIR_Rating

select b.Resource, SUM(cost_actual)
from PMV37000 a inner join PMV37010 b
on a.Project = b.Project
group by b.Resource

[/code]

How can I get this bottom query to work in the top query where the sum(cost_actual) is. In other words, The result of the bottom query is what I want as the input to the column of the top insert where sum(cost_actual) is. Am i going about this right? Am new to this.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-04-01 : 18:48:29

Looks like the original query inserts into 13 fields, and your query only selects 2. As long as the other fields in CompletedTable are not required, you should be able to specify only the two fields to insert into which your query selects.

Some days you're the dog, and some days you're the fire hydrant.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-02 : 08:13:42

Try this:


-- CREATE TEMPORARY TABLE WITH COSTS PER RESOURCE
select 
	 b.Resource
	,SUM(cost_actual) as total_costs

into #temp

from PMV37000  as a 
inner join PMV37010 b
on a.Project = b.Project
group by b.Resource


insert into CompletedTable (Resource_Name,Project_Name,Project_Actual, OT, OB, 
OQ, DRE, PIR, OverAll_Actual_OT, OverAll_Actual_OB, OverAll_Actual_OQ, OverAll_Actual_DRE, OverAll_Actual_PIR)


-- JOIN TEMPORARY TABLE WITH PMV Tables ON RESOURCE !!!!
select 
	 b.Resource
	,a.Project
	,Cost_Actual 
	,Case When Actual_Install_Date <= Projected_Install_Date then 1 else 0 end
	,case when cost_Actual <= (Cost_Budget * 1.1) then 1 else 0 end
	,OQ_Rating
	,DRE_Rating
	,PIR_Rating 
	,t.total_costs as OverALL_Actual_OT
	,t.total_costs as OverAll_Actual_OB
	,t.total_costs as OverAll_Actual_OQ
	,t.total_costs as OverAll_Actual_DRE
	,t.total_costs as OverAll_Atual_PIR

from PMV37000 a 
inner join PMV37010 b 
on a.Project= b.Project
inner join #temp as t
on t.resources = b.resources

i've created temporary table to store summed values per resource. you join this table in insert-Select statement.
Please note: since i'm not familiar with your data nor DDL, please check if code make sense for your business (data) model.

All best

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources