表值参数加Merge ..into 执行9万条数据insert（update）

   接着上面9万条并行  博客，这次采用的方法经试验比那个快很多。大概2分16秒9万条数据insert或者update 。

   两个方法对比：

    1.并行+表值参数   时间相对较长，但是如果有数据不符合，只有不符合数据插入失败，不影响其他数据插入。

    2.Merge ..into      时间短，但是如果有数据不符合，影响其他数据插入，整体插入失败。

    存储过程代码展示：

    

--第二种方案(C#+存储过程（MERGE方案）) t1 是meterday t2是datatable
BEGIN

MERGE INTO dbo.WR_FreezeDay T1
USING (SELECT meter_no,freezedate,flow,dreaddate FROM @temp ) t2
ON (T1.iMeterID=(select iMeterID from WR_Meters where tMeterAddr= t2.meter_no and iMeterManufactureID='1') and dFreezeDate=t2.freezedate)
WHEN MATCHED and T1.iMeterID is not null THEN
UPDATE SET fFreezeNumber=t2.flow,fUseNumber=(t2.flow-(select top 1 fFreezeNumber from WR_FreezeDay where iMeterID=(select iMeterID from WR_Meters where tMeterAddr= t2.meter_no and iMeterManufactureID='1')
and dFreezeDate <t2.freezedate order by dFreezeDate desc)),dFactDate=GETDATE(),dReadDate=t2.dreaddate
WHEN NOT MATCHED THEN
INSERT (iMeterID,dFreezeDate,fFreezeNumber,fUseNumber,dFactDate,dReadDate)
VALUES((select iMeterID from WR_Meters where tMeterAddr= t2.meter_no and iMeterManufactureID='1'),t2.freezedate,t2.flow,
(t2.flow-(select top 1 fFreezeNumber from WR_FreezeDay
where iMeterID=(select iMeterID from WR_Meters where tMeterAddr= t2.meter_no and iMeterManufactureID='1')
and dFreezeDate <t2.freezedate order by dFreezeDate desc)),getdate(),t2.dreaddate) ;

MERGE INTO WR_FreezeMonth T1
USING (SELECT meter_no,freezeMonth,flow,dreaddate FROM @temp ) t2
ON (T1.iMeterID=(select iMeterID from WR_Meters where tMeterAddr= t2.meter_no and iMeterManufactureID='1') and tFreezeMonth=t2.freezeMonth)
WHEN MATCHED and T1.iMeterID is not null THEN
UPDATE SET fFreezeNumber=t2.flow,fUseNumber=(t2.flow-(select top 1 fFreezeNumber from WR_FreezeMonth where iMeterID=(select iMeterID from WR_Meters where tMeterAddr= t2.meter_no and iMeterManufactureID='1')
and tFreezeMonth <t2.freezeMonth order by tFreezeMonth desc)),dFactDate=GETDATE(),dReadDate=t2.dreaddate
WHEN NOT MATCHED THEN
INSERT (iMeterID,tFreezeMonth,fFreezeNumber,fUseNumber,dFactDate,dReadDate)
VALUES((select iMeterID from WR_Meters where tMeterAddr= t2.meter_no and iMeterManufactureID='1'),t2.freezeMonth,t2.flow,
(t2.flow-(select top 1 fFreezeNumber from WR_FreezeMonth
where iMeterID=(select iMeterID from WR_Meters where tMeterAddr= t2.meter_no and iMeterManufactureID='1')
and tFreezeMonth <t2.freezeMonth order by tFreezeMonth desc)),getdate(),t2.dreaddate) ;



END

 

 

C#   代码：