下面是一段导入数据的sql存储过程。但是我不是很明白意思。高手给我解释解释。求解啊。ALTER PROCEDURE [dbo].[prcStoneCheckIsC]
-- Add the parameters for the stored procedure here
@vSCId varchar(25),
@vSTypeId varchar(25)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--先插入到临时表
SELECT SCId,StgLocId,StoneId,SCNum,CNum,Balance,Weight,Price INTO #TempStoneCheck FROM StoneCheckDetail WHERE SCID=@vSCId
--修改相同的数据
UPDATE #TempStoneCheck SET CNum=B.CNum FROM #TempStoneCheck A,
(SELECT STypeId,StgLocId,StoneId,SUM(CNum) as CNum FROM StoneStg Group by STypeId,StgLocId,StoneId) B
WHERE A.StgLocId=B.StgLocId AND A.StoneId=B.StoneId AND STypeId=@vSTypeId AND B.CNum>0 AND A.SCId=@vSCId
--插入不同的数据
INSERT INTO #TempStoneCheck SELECT @vSCId,B.StgLocId,B.StoneId,0,B.CNum,0,B.Price,B.Weight FROM
(SELECT STypeId,StgLocId,StoneId,SUM(CNum) as CNum,AVG(Price) as Price,AVG(Weight) AS Weight FROM StoneStg WHERE STypeId=@vSTypeId
Group by STypeId,StgLocId,StoneId) B
WHERE B.STypeId+B.StgLocId NOT IN(SELECT STypeId+StgLocId FROM #TempStoneCheck A
WHERE A.StoneId=B.StoneId AND StgLocId=B.StgLocId AND SCId=@vSCId) AND B.CNum>0
--计算差额
UPDATE #TempStoneCheck SET Balance=SCNum-CNum
--删除盘点明细的数据
DELETE FROM StoneCheckDetail WHERE SCID=@vSCId
--把临时表的数据插入到盘点明细
INSERT INTO StoneCheckDetail SELECT * FROM #TempStoneCheck
--删除临时表
DROP TABLE #TempStoneCheckEND
-- Add the parameters for the stored procedure here
@vSCId varchar(25),
@vSTypeId varchar(25)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--先插入到临时表
SELECT SCId,StgLocId,StoneId,SCNum,CNum,Balance,Weight,Price INTO #TempStoneCheck FROM StoneCheckDetail WHERE SCID=@vSCId
--修改相同的数据
UPDATE #TempStoneCheck SET CNum=B.CNum FROM #TempStoneCheck A,
(SELECT STypeId,StgLocId,StoneId,SUM(CNum) as CNum FROM StoneStg Group by STypeId,StgLocId,StoneId) B
WHERE A.StgLocId=B.StgLocId AND A.StoneId=B.StoneId AND STypeId=@vSTypeId AND B.CNum>0 AND A.SCId=@vSCId
--插入不同的数据
INSERT INTO #TempStoneCheck SELECT @vSCId,B.StgLocId,B.StoneId,0,B.CNum,0,B.Price,B.Weight FROM
(SELECT STypeId,StgLocId,StoneId,SUM(CNum) as CNum,AVG(Price) as Price,AVG(Weight) AS Weight FROM StoneStg WHERE STypeId=@vSTypeId
Group by STypeId,StgLocId,StoneId) B
WHERE B.STypeId+B.StgLocId NOT IN(SELECT STypeId+StgLocId FROM #TempStoneCheck A
WHERE A.StoneId=B.StoneId AND StgLocId=B.StgLocId AND SCId=@vSCId) AND B.CNum>0
--计算差额
UPDATE #TempStoneCheck SET Balance=SCNum-CNum
--删除盘点明细的数据
DELETE FROM StoneCheckDetail WHERE SCID=@vSCId
--把临时表的数据插入到盘点明细
INSERT INTO StoneCheckDetail SELECT * FROM #TempStoneCheck
--删除临时表
DROP TABLE #TempStoneCheckEND
UPDATE #TempStoneCheck SET CNum=B.CNum FROM #TempStoneCheck A,
(SELECT STypeId,StgLocId,StoneId,SUM(CNum) as CNum FROM StoneStg Group by STypeId,StgLocId,StoneId) B
WHERE A.StgLocId=B.StgLocId AND A.StoneId=B.StoneId AND STypeId=@vSTypeId AND B.CNum>0 AND A.SCId=@vSCId
这句啦。。头晕啊。
如上例SQL, 先弄清楚A表B表各是什么数据.