看语句应该是不会.改成这样试一下: insert into #tmpCapitalPlanQuery(FiscalYear, FiscalPeriod, WareHouseID,WareHouseName,TotalBalance, PlanBalance) select ......
什么意思?为什么要把IDENTITY_INSERT设成 OFF,谢谢!
TRY:create table #tmpCapitalPlanQuery ( row int identity(1,1), FiscalYear nvarchar(4), FiscalPeriod nvarchar(2), WareHouseID utCode, WareHouseName nvarchar(100), TotalBalance UTPRICE, PlanBalance UTPRICE ) ------------------------------------ insert into #tmpCapitalPlanQuery (fiscalYear,fiscalPeriod,warehouseid,warehouseName,totalbalance,planbalance) select inv.FiscalYear, inv.FiscalPeriod, inv.warehouseid, (select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename, sum(inv.CurQuanBalance*pri.price) as TotalBalance, FP.Fund as Fund from invbalanceAccount inv left join materialprice pri on inv.CompanyID=pri.CompanyID and inv.MaterialID=pri.MaterialID left join StockFundPlans FP on inv.CompanyID=FP.CompanyID and inv.FiscalYear=FP.FiscalYear and inv.FiscalPeriod=FP.FiscalPeriod and inv.WareHouseID=FP.WareHouseID where inv.WareHouseID='00000000000000000001' group by inv.FiscalYear,inv.FiscalPeriod,inv.WareHouseID,FP.Fund order by inv.FiscalYear+inv.FiscalPeriod
嗯,对,我试了,如果我把IDENTITY_INSERT设成OFF的话,然后跟上字段的名称,这样就可以插入了。可我还是不明白,我一开始的IDENTITY_INSERT是ON的时候,为什么我不带字段就插不进去,而且我的这种类似的INSERT是调用了两次的,通过IF语句进行的判断,第一次没问题,可以INSERT,第二次为什么就出现这种提示,有没有高手能解释一下。 我的两次调用基本一样 set @SQL=N' insert into #tmpCapitalPlanQuery select inv.FiscalYear,inv.FiscalPeriod,inv.warehouseid, (select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename, sum(inv.CurQuanBalance*pri.price) as TotalBalance, FP.Fund as PlanBalance from invbalanceAccount inv left join materialprice pri on inv.CompanyID=pri.CompanyID and inv.MaterialID=pri.MaterialID left join StockFundPlans FP on inv.CompanyID=FP.CompanyID and inv.FiscalYear=FP.FiscalYear and inv.FiscalPeriod=FP.FiscalPeriod and inv.WareHouseID=FP.WareHouseID where '+ @strWhere + N' And inv.FiscalYear='''+ @FiscalYear+N''' And inv.FiscalPeriod='''+ @FiscalPeriod+N''' group by inv.FiscalYear,inv.FiscalPeriod,inv.WareHouseID,FP.Fund'; exec(@SQL)这是第一次调用,看样子跟第一次没有什么区别,只是WHERE条件上有所区别,谢谢!
select inv.FiscalYear, inv.FiscalPeriod, inv.warehouseid, (select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename, sum(inv.CurQuanBalance*pri.price) as TotalBalance, FP.Fund as Fund into #tmpCapitalPlanQuery from invbalanceAccount inv left join materialprice pri on inv.CompanyID=pri.CompanyID and inv.MaterialID=pri.MaterialID left join StockFundPlans FP on inv.CompanyID=FP.CompanyID and inv.FiscalYear=FP.FiscalYear and inv.FiscalPeriod=FP.FiscalPeriod and inv.WareHouseID=FP.WareHouseID where inv.WareHouseID='00000000000000000001' group by inv.FiscalYear,inv.FiscalPeriod,inv.WareHouseID,FP.Fund order by inv.FiscalYear+inv.FiscalPeriod ALTER TABLE #tmpCapitalPlanQuery ADD row bigint identity(1,1) not null
只要你STLECT后面是六项,绝对不会报你前面的错误。 如果是七项就出错了。insert into #tmpCapitalPlanQuery select inv.FiscalYear, inv.FiscalPeriod, inv.warehouseid, (select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename, sum(inv.CurQuanBalance*pri.price) as TotalBalance, FP.Fund as Fund from ...... 上面是六项,不会出错。 如果你下面语句写错,造成超过六项。就一定出错。你先不用insert 只用select 看一下,你原来的语句SELECT出来是不是六项。你就知道错在那了。
set @SQL=N' --insert into #tmpCapitalPlanQuery select inv.FiscalYear,inv.FiscalPeriod,inv.warehouseid, (select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename, sum(inv.CurQuanBalance*pri.price) as TotalBalance, FP.Fund as PlanBalance from invbalanceAccount inv left join materialprice pri on inv.CompanyID=pri.CompanyID and inv.MaterialID=pri.MaterialID left join StockFundPlans FP on inv.CompanyID=FP.CompanyID and inv.FiscalYear=FP.FiscalYear and inv.FiscalPeriod=FP.FiscalPeriod and inv.WareHouseID=FP.WareHouseID where '+ @strWhere + N' And inv.FiscalYear='''+ @FiscalYear+N''' And inv.FiscalPeriod='''+ @FiscalPeriod+N''' group by inv.FiscalYear,inv.FiscalPeriod,inv.WareHouseID,FP.Fund'; exec(@SQL)
insert into #tmpCapitalPlanQuery(FiscalYear,
FiscalPeriod,
WareHouseID,WareHouseName,TotalBalance,
PlanBalance)
select ......
(
row int identity(1,1),
FiscalYear nvarchar(4),
FiscalPeriod nvarchar(2),
WareHouseID utCode,
WareHouseName nvarchar(100),
TotalBalance UTPRICE,
PlanBalance UTPRICE
)
------------------------------------
insert into #tmpCapitalPlanQuery (fiscalYear,fiscalPeriod,warehouseid,warehouseName,totalbalance,planbalance)
select
inv.FiscalYear,
inv.FiscalPeriod,
inv.warehouseid,
(select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename,
sum(inv.CurQuanBalance*pri.price) as TotalBalance,
FP.Fund as Fund
from invbalanceAccount inv
left join materialprice pri
on inv.CompanyID=pri.CompanyID and inv.MaterialID=pri.MaterialID
left join StockFundPlans FP
on inv.CompanyID=FP.CompanyID and inv.FiscalYear=FP.FiscalYear
and inv.FiscalPeriod=FP.FiscalPeriod and inv.WareHouseID=FP.WareHouseID
where inv.WareHouseID='00000000000000000001'
group by inv.FiscalYear,inv.FiscalPeriod,inv.WareHouseID,FP.Fund
order by inv.FiscalYear+inv.FiscalPeriod
这样插入数据的时候,Table名后需要列出插入的列名。
我的两次调用基本一样
set @SQL=N'
insert into #tmpCapitalPlanQuery
select inv.FiscalYear,inv.FiscalPeriod,inv.warehouseid,
(select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename,
sum(inv.CurQuanBalance*pri.price) as TotalBalance,
FP.Fund as PlanBalance
from invbalanceAccount inv
left join materialprice pri
on inv.CompanyID=pri.CompanyID and inv.MaterialID=pri.MaterialID
left join StockFundPlans FP
on inv.CompanyID=FP.CompanyID and inv.FiscalYear=FP.FiscalYear
and inv.FiscalPeriod=FP.FiscalPeriod and inv.WareHouseID=FP.WareHouseID
where '+ @strWhere + N' And inv.FiscalYear='''+ @FiscalYear+N''' And inv.FiscalPeriod='''+ @FiscalPeriod+N'''
group by inv.FiscalYear,inv.FiscalPeriod,inv.WareHouseID,FP.Fund';
exec(@SQL)这是第一次调用,看样子跟第一次没有什么区别,只是WHERE条件上有所区别,谢谢!
inv.FiscalYear,
inv.FiscalPeriod,
inv.warehouseid,
(select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename,
sum(inv.CurQuanBalance*pri.price) as TotalBalance,
FP.Fund as Fund into #tmpCapitalPlanQuery
from invbalanceAccount inv
left join materialprice pri
on inv.CompanyID=pri.CompanyID and inv.MaterialID=pri.MaterialID
left join StockFundPlans FP
on inv.CompanyID=FP.CompanyID and inv.FiscalYear=FP.FiscalYear
and inv.FiscalPeriod=FP.FiscalPeriod and inv.WareHouseID=FP.WareHouseID
where inv.WareHouseID='00000000000000000001'
group by inv.FiscalYear,inv.FiscalPeriod,inv.WareHouseID,FP.Fund
order by inv.FiscalYear+inv.FiscalPeriod
ALTER TABLE #tmpCapitalPlanQuery ADD row bigint identity(1,1) not null
如果是七项就出错了。insert into #tmpCapitalPlanQuery
select
inv.FiscalYear,
inv.FiscalPeriod,
inv.warehouseid,
(select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename,
sum(inv.CurQuanBalance*pri.price) as TotalBalance,
FP.Fund as Fund
from ......
上面是六项,不会出错。
如果你下面语句写错,造成超过六项。就一定出错。你先不用insert 只用select 看一下,你原来的语句SELECT出来是不是六项。你就知道错在那了。
--insert into #tmpCapitalPlanQuery
select inv.FiscalYear,inv.FiscalPeriod,inv.warehouseid,
(select warehousename from Warehouses where warehouseid=inv.warehouseid) as warehousename,
sum(inv.CurQuanBalance*pri.price) as TotalBalance,
FP.Fund as PlanBalance
from invbalanceAccount inv
left join materialprice pri
on inv.CompanyID=pri.CompanyID and inv.MaterialID=pri.MaterialID
left join StockFundPlans FP
on inv.CompanyID=FP.CompanyID and inv.FiscalYear=FP.FiscalYear
and inv.FiscalPeriod=FP.FiscalPeriod and inv.WareHouseID=FP.WareHouseID
where '+ @strWhere + N' And inv.FiscalYear='''+ @FiscalYear+N''' And inv.FiscalPeriod='''+ @FiscalPeriod+N'''
group by inv.FiscalYear,inv.FiscalPeriod,inv.WareHouseID,FP.Fund';
exec(@SQL)
这样就很容易看出错在那。