请教解析

[fanbo]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USP_xm_dtcb_rpt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[USP_xm_dtcb_rpt]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE dbo.USP_xm_dtcb_rpt(@strProjectCode varchar(40))
WITH ENCRYPTION
AS

SELECT project_code,
project_name,
cost_code,
cost_name,
(case when ht_cost.cifnext = 1 then space((ht_cost.clevel - 1)*2) + ht_cost.cost_name
else space((ht_cost.clevel - 1)*2) + ht_cost.cost_name end) as new_cost_name,
target_cost,
dt_cost,
' ' as jysm,
cifnext,
CONVERT(VARCHAR(80),' ') as yf_unit,
CONVERT(VARCHAR(80),' ') as ht_code,
CONVERT(VARCHAR(80),' ') as ht_name,
0 as ht_amount,
0 as bg_amount,
0 as sf_amount,
( select isnull(sum(cf_amount),0)
from cfmx
where CHARINDEX(@strProjectCode + '.' ,cfmx.project_code + '.') = 1
and CHARINDEX(ht_cost.cost_code + '.' ,cfmx.km_code + '.') = 1
and (cfmx.cfly ='合同' or cfmx.cfly ='材料')) as htcf_amount,
( select isnull(sum(cf_amount),0)
from cfmx
where CHARINDEX(@strProjectCode + '.' ,cfmx.project_code + '.') = 1
and CHARINDEX(ht_cost.cost_code + '.' ,cfmx.km_code + '.') = 1
and cfmx.cfly ='变更') as bgcf_amount,
( select isnull(sum(cf_amount),0)
from cfmx
where CHARINDEX(@strProjectCode + '.' ,cfmx.project_code + '.') = 1
and CHARINDEX(ht_cost.cost_code + '.' ,cfmx.km_code + '.') = 1
and cfmx.cfly ='实付') as htsf_amount
INTO #DTCB_TABLE
FROM ht_cost
WHERE project_code = @strProjectCode
ORDER BY cost_code

INSERT INTO #DTCB_TABLE
SELECT @strProjectCode as project_code,
(select xm_name from ht_xm_setup where xm_code = @strProjectCode) as project_name,
cfmx.km_code as cost_code,
cfmx.km_name as cost_name,
(select new_cost_name from #DTCB_TABLE
where cost_code = cfmx.km_code) as new_cost_name,
(select target_cost from ht_cost
where project_code = @strProjectCode
and cost_code = cfmx.km_code) as target_cost,
(select dt_cost from ht_cost
where project_code = @strProjectCode
and cost_code = cfmx.km_code) as dt_cost,
'' as jysm,
'9' as cifnext,
ht_mng.yf_unit,
ht_mng.ht_code,
ht_mng.ht_name,
ht_mng.ht_amount,
ht_mng.sum_bgamount,
ht_mng.sum_sfamount,
sum(Case When cfly ='合同' or cfly ='材料' Then cf_amount Else 0 End ) as htcf_amount,
sum(Case When cfly ='变更' Then cf_amount Else 0 End ) as bgcf_amount,
sum(Case When cfly ='实付' Then cf_amount Else 0 End ) as htsf_amount
FROM ht_mng,cfmx
WHERE ht_mng.ht_code = cfmx.ht_code
AND CHARINDEX(@strProjectCode + '.', cfmx.project_code + '.') = 1
GROUP BY cfmx.km_code,cfmx.km_name,ht_mng.yf_unit,ht_mng.ht_code,ht_mng.ht_name,ht_mng.ht_amount,ht_mng.sum_bgamount,ht_mng.sum_sfamount

SELECT * FROM #DTCB_TABLE ORDER BY COST_CODE,HT_CODE,PROJECT_CODE

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



这个存储过程请高手解析一下，我主要想知道的是 select ....into 与 insert into 的主要区别。

[btlyeo]

insert into 后面只能指定已经传好值的参数比如

insert into table (ziduan1,ziduan2) values('11','222')

而select ... into
可以把所有符合条件的值插入表，前提是列项目必须一一对应

[huailairen]

select into 自动创建表结构，并插入数据，而且要插入数据的表在执行select into之前应该不存在。
insert into 必须自己创建表才能插入数据。

[itblog]

select ...into 是数据库中没有#DTCB_TABLE这个临时表,而在查询的时候自动生成一个临时表,
insert into 是已经存在临时表并向里面插入记录

[fanbo]

SELECT project_code,
project_name,
cost_code,
cost_name,
(case when ht_cost.cifnext = 1 then space((ht_cost.clevel - 1)*2) + ht_cost.cost_name
else space((ht_cost.clevel - 1)*2) + ht_cost.cost_name end) as new_cost_name,
target_cost,
dt_cost,
' ' as jysm,
cifnext,
CONVERT(VARCHAR(80),' ') as yf_unit,
CONVERT(VARCHAR(80),' ') as ht_code,
CONVERT(VARCHAR(80),' ') as ht_name,
0 as ht_amount,
0 as bg_amount,
0 as sf_amount,
( select isnull(sum(cf_amount),0)
from cfmx
where CHARINDEX(@strProjectCode + '.' ,cfmx.project_code + '.') = 1
and CHARINDEX(ht_cost.cost_code + '.' ,cfmx.km_code + '.') = 1
and (cfmx.cfly ='合同' or cfmx.cfly ='材料')) as htcf_amount,
( select isnull(sum(cf_amount),0)
from cfmx
where CHARINDEX(@strProjectCode + '.' ,cfmx.project_code + '.') = 1
and CHARINDEX(ht_cost.cost_code + '.' ,cfmx.km_code + '.') = 1
and cfmx.cfly ='变更') as bgcf_amount,
( select isnull(sum(cf_amount),0)
from cfmx
where CHARINDEX(@strProjectCode + '.' ,cfmx.project_code + '.') = 1
and CHARINDEX(ht_cost.cost_code + '.' ,cfmx.km_code + '.') = 1
and cfmx.cfly ='实付') as htsf_amount
INTO #DTCB_TABLE
FROM ht_cost
WHERE project_code = @strProjectCode
ORDER BY cost_code

这段的语句是说明建立数据库表的同时也插入数据？但是前提是数据库中不可以有#DTCB_TABLE这个虚表！

INSERT INTO #DTCB_TABLE
SELECT @strProjectCode as project_code,
(select xm_name from ht_xm_setup where xm_code = @strProjectCode) as project_name,
cfmx.km_code as cost_code,
cfmx.km_name as cost_name,
(select new_cost_name from #DTCB_TABLE
where cost_code = cfmx.km_code) as new_cost_name,
(select target_cost from ht_cost
where project_code = @strProjectCode
and cost_code = cfmx.km_code) as target_cost,
(select dt_cost from ht_cost
where project_code = @strProjectCode
and cost_code = cfmx.km_code) as dt_cost,
'' as jysm,
'9' as cifnext,
ht_mng.yf_unit,
ht_mng.ht_code,
ht_mng.ht_name,
ht_mng.ht_amount,
ht_mng.sum_bgamount,
ht_mng.sum_sfamount,
sum(Case When cfly ='合同' or cfly ='材料' Then cf_amount Else 0 End ) as htcf_amount,
sum(Case When cfly ='变更' Then cf_amount Else 0 End ) as bgcf_amount,
sum(Case When cfly ='实付' Then cf_amount Else 0 End ) as htsf_amount
FROM ht_mng,cfmx
WHERE ht_mng.ht_code = cfmx.ht_code
AND CHARINDEX(@strProjectCode + '.', cfmx.project_code + '.') = 1
GROUP BY cfmx.km_code,cfmx.km_name,ht_mng.yf_unit,ht_mng.ht_code,ht_mng.ht_name,ht_mng.ht_amount,ht_mng.sum_bgamount,ht_mng.sum_sfamount

那么这段语句岂非是将上面所有的插入值都冲掉了吗？还是在上面该虚表的结尾继续插入值？

[itblog]

在上面的虚表的结尾继续插入值