为已存在的表增加字段和补数据

SELECT * FROM  price WHERE ddate >=CONVERT(varchar(100), dateadd(month, -1, getdate()), 120) 

查询上月的今日日期 且格式化为yyyymm

-- 主计量单位插入
INSERT INTO base_storage_inrecord_copy1 
(id,mid,code,num,warehouse,ctime,batch,orderid,unit,orderno,ordertype,postion,createuserid,cfree1,cfree2,cfree3,unittype,unit1,num2,converts)
SELECT si.id,si.mid,si.code,si.num,si.warehouse,si.ctime,si.batch,si.orderid,si.unit,si.orderno,si.ordertype,si.postion,si.createuserid,si.cfree1,si.cfree2,si.cfree3,si.unittype,sd.cInvA_Unit,case when isnull(d.iChangRate,0) = 0 then 0 else (isnull(si.num,0)/isnull(d.iChangRate,0)) end as num2,d.iChangRate   
from base_storage_inrecord si
left join base_stock_detail sd ON si.code = sd.code 
left join [UFDATA_001_2019].dbo.Inventory b on si.code=b.cInvCode
left join [UFDATA_001_2019].dbo.ComputationGroup c on b.cGroupCode=c.cGroupCode
left join [UFDATA_001_2019].dbo.ComputationUnit d on b.cComUnitCode=d.cComunitCode
WHERE si.unit = sd.cComUnitName
-- 辅计量单位插入
INSERT INTO base_storage_inrecord_copy1 
(id,mid,code,num,warehouse,ctime,batch,orderid,unit,orderno,ordertype,postion,createuserid,cfree1,cfree2,cfree3,unittype,unit1,num2,converts)
SELECT si.id,si.mid,si.code,case when isnull(d.iChangRate,0) = 0 then 0 else (isnull(si.num,0)*isnull(d.iChangRate,0)) end as num,si.warehouse,si.ctime,si.batch,si.orderid,sd.cComUnitName,si.orderno,si.ordertype,si.postion,si.createuserid,si.cfree1,si.cfree2,si.cfree3,si.unittype,si.unit,si.num,d.iChangRate
from base_storage_inrecord si
left join base_stock_detail sd ON si.code = sd.code 
left join [UFDATA_001_2019].dbo.Inventory b on si.code=b.cInvCode
left join [UFDATA_001_2019].dbo.ComputationGroup c on b.cGroupCode=c.cGroupCode
left join [UFDATA_001_2019].dbo.ComputationUnit d on b.cComUnitCode=d.cComunitCode
WHERE si.unit = sd.cInvA_Unit

遇到之前的表需要加字段 ，需要把原来的数据里面的这些字段填充且更改一些字段，所以预防错误的我，先INSERT INTO 临时表 select 。。 去把数据先生成出来查看 注意：有除法时记得非0 非null 如：,case when isnull(d.iChangRate,0) = 0 then 0 else (isnull(si.num,0)*isnull(d.iChangRate,0)) end as num
用case when 和isnull完美结合。