请问一个SQL存储过程

请教一个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)]asbegininsert into ICBatchNoRule(FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail)SELECT FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetailfrommaterials 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 materialswhere 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，......


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


MARK:虽然有了解决办法，但是你这么做的目的是什么.一头雾水~

[解决办法]
你可以直接把A语句的查询结果 当成某张表  然后与其它表关联  insert时  根据条件插入数据  


INSERT INTO ICBatchNoRule
       ( FID ,
         Fitemid ,
         FPropertyid ,
         Fuseshortnumber ,
         FFormatDate ,
         FSelDefine ,
         FWidth ,
         Fcharacter ,
         FSubcharacter ,
         FDetail
       )
SELECT *  
FROM
(SELECT  *
FROM    materials
WHERE   Fbatchman = 1
       AND fclsid IN ( 1, 3 )
       AND fitemid NOT IN ( SELECT fitemid
                            FROM   icbatchnorule )) T
  INNER JOIN 表名
[解决办法]
SQL codecreate proc ProcName[(ParmName ParmType)]asbegin--递增序列declare @idd int,declare @FID intdeclare @FPropertyid int--获取当前最大序列号select @idd=max(FPropertyid) from FPropertyiddeclare cursor1 cursor for         --定义游标cursor1select * from materials  where Fbatchman=1 and fclsid in (1,3) and fitemid not in (select fitemid from icbatchnorule)             --使用游标的对象(跟据需要填入select文)open cursor1                       --打开游标fetch next from cursor1 into @FID,@FPropertyid  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中while @@fetch_status=0           --判断是否成功获取数据beginset @idd=@idd+1;insert into ICBatchNoRule(FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail)select @FID,@idd,@FPropertyid ............ --对应位置字段from materialsfetch next from cursor1 into @FID,@FPropertyid   --将游标向下移1行endclose cursor1                   --关闭游标end
[解决办法]
create proc ProcName[(ParmName ParmType)]
as
begin

--递增序列
declare @idd int,

declare @FID int
declare @FPropertyid int
--....declare声明其他变量，需要插入的字段，如果所有的可以用*号代替

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

declare cursor1 cursor for         --定义游标cursor1
select FID,FPropertyid，.... from materials  where Fbatchman=1 and fclsid in (1,3) and fitemid not in (select fitemid from icbatchnorule)             --使用游标的对象(跟据需要填入select文)
open cursor1                       --打开游标

fetch next from cursor1 into @FID,@FPropertyid  --将游标向下移1行，获取的数据放入之前定义的变量@id,@name中

while @@fetch_status=0           --判断是否成功获取数据
begin

set @idd=@idd+1;
insert into ICBatchNoRule(FID,Fitemid,FPropertyid,Fuseshortnumber,FFormatDate,FSelDefine,FWidth,Fcharacter,FSubcharacter,FDetail)
select @FID,@idd,@FPropertyid ............ --对应位置字段
from materials

fetch next from cursor1 into @FID,@FPropertyid   --将游标向下移1行
end

close cursor1                   --关闭游标

end
[解决办法]
---你改成对应对应位置的字段！！！不带这么教人写代码的，我哭了，你自己想想吧

请问一个SQL存储过程

热点推荐