请教一个复杂点的sql语句
有一个要写入数据的表IndustryScore结构：
IndustryCode nvarchar
CapableId int
KeyId int
MaxScore float
MinScore float
AvaScore float
其中前三个是主键，
通过
SELECT   vocationCode,   CapId,   KeyId,MAX(Data)   AS   maxS,   MIN(Data)   AS   minS,   AVG(Data)   AS   avgS            
FROM   CompanyKeyData
GROUP   BY   CapId,   KeyId,   vocationCode
检索出的结果要写入IndustryScore,如果IndustryCode,CapableId, KeyId三个值数据库中已经有相同的了，则对相应的值进行修改，如果
没有，则执行insert操作，如何用一条语句来写成，谢谢，在线等。。。

------解决方案--------------------
Create Proc dbo.P_WriteIndustryScore

as

set nocount on

Insert Into IndustryScore
SELECT vocationCode, CapId, KeyId,MAX(Data) AS maxS, MIN(Data) AS minS, AVG(Data) AS avgS
FROM CompanyKeyData as pqh
Where not Exists(select 1 from IndustryScore as pqs
where pqh.IndustryCode=pqs.vocationCode
and pqs.CapableId=pqh.CapId and pqs.KeyId=pqh.KeyId)
GROUP BY pqh.CapId,pqh.KeyId,pqh.vocationCode


update IndustryScore
set MaxScore=MAX(Data),MinScore=MIN(Data),AvaScore=AVG(Data)
FROM CompanyKeyData
where
CompanyKeyData.IndustryCode=IndustryScore.vocationCode
and IndustryScore.CapableId=CompanyKeyData.CapId
and CompanyKeyData.KeyId=IndustryScore.KeyId)
GROUP BY CompanyKeyData.CapId, CompanyKeyData.KeyId, CompanyKeyData.vocationCode


set nocount off
go
------解决方案--------------------
1、insert

insert into IndustryScore
select * from
(
SELECT vocationCode, CapId, KeyId,MAX(Data) AS maxS, MIN(Data) AS minS, AVG(Data) AS avgS
FROM CompanyKeyData
GROUP BY CapId, KeyId, vocationCode
) t
where cast(vocationCode as varchar) + cast(CapId as varchar) + cast(KeyId as varchar) not in
(select cast(IndustryCode as varchar) + cast(CapableId as varchar) + cast(KeyId as varchar) from IndustryScore)

2、update
update IndustryScore
set MaxScore = t.maxS,
MinScore = t.minS,
AvaScore = t.avgs
from IndustryScore m,
(
SELECT vocationCode, CapId, KeyId,MAX(Data) AS maxS, MIN(Data) AS minS, AVG(Data) AS avgS
FROM CompanyKeyData
GROUP BY CapId, KeyId, vocationCode
) t
where m.IndustryCode = t.vocationCode and m.CapableId = t.CapId and m.KeyId = t.KeyId

------解决方案--------------------
我觉得插入难度大了点，直接做一个左连接插入到新表行不行？
比如这样：select ……insert newtable form table1 left join table2 on table1。field＝table2.field