日期:2014-05-18  浏览次数:20339 次

请教一个SQL存储过程
我想把如下查询A语句的记录集插入到icbatchnorule表,如果查询A语句后记录多的话,有没有快速的办法让它自动插入到icbatchnorule表中? 请教各位。

各注:icbatchnorule表的Fitemid字段是唯一值,不会重复。


A:select * from materials where Fbatchman=1 and fclsid in (1,3) and fitemid not in (select fitemid from icbatchnorule)


B: insert into ICBatchNoRule(FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail)
Values(1,28734,8,0,'','',6,'x','',1)

------解决方案--------------------
lz:基于你的问题的解答:

1。你可以这样保存结果到你的目的表中:
insert into ICBatchNoRule(FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail)
Values(1,28734,8,0,'','',6,'x','',1)
select * from materials where Fbatchman=1 and fclsid in (1,3) and fitemid not in (select fitemid from icbatchnorule)

先试下如果有问题再贴出来。
希望你成功。

------解决方案--------------------
如果 Fitemid 是自增列。那么你就没必要自己插入一个字段。你再插入其他数据的时候他就自动增加

如果 Fitemid 是自己定义好的ROWID。那么也你也只需要将@Fitemid 插入即可。 

------解决方案--------------------
SQL code
create proc ProcName[(ParmName ParmType)]
as
begin

insert into ICBatchNoRule(FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail)
SELECT FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail
from
materials where Fbatchman=1 and fclsid in (1,3) and fitemid not in (select fitemid from icbatchnorule)

end

------解决方案--------------------
icbatchnorule表已经存在:
INSERT INTO icbatchnorule
select FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail from materials where Fbatchman=1 and fclsid in (1,3) and fitemid not in (select fitemid from icbatchnorule)
icbatchnorule表不存在:
select FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail
INTO icbatchnorule from materials where Fbatchman=1 and fclsid in (1,3) and fitemid not in (select fitemid from icbatchnorule)
------解决方案--------------------
SQL code

/*
用 “insert into tablename(col1, col2)
select col1='1111', col2='222' from othertable”模式
*/
insert into ICBatchNoRule(FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail)
select FID,Fitemid, ............ --这里写入对应上一行对应位置的字段
from materials
where Fbatchman=1 and fclsid in (1,3) and fitemid not in (select fitemid from icbatchnorule)

------解决方案--------------------
探讨

引用:

icbatchnorule表已经存在:
INSERT INTO icbatchnorule
select FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail from materials where Fbat……

------解决方案--------------------
探讨

当把以下三条记录插入icbhatchnorule表是,只有Fitemid字段值会变,其它字段值一样。
例如:
insert into ICBatchNoRule(FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail)
Values(1,28……

------解决方案--------------------

--递增序列

declare @idd int,

--获取当前最大序列号
select @idd=max(FPropertyid) from FPropertyid

--逐条执行
insert into ICBatchNoRule(FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail)
SELECT FID, @idd+1,......


--如果多条记录一次执行,你用游标,上述字段全部设置变量