我需要使用一列中具有不同值的触发器像1000一样批量插入行

debugcn 发表于 Dev

用户名

这是触发因素

CREATE TRIGGER [dbo].[Teacher]
ON [dbo].[Teacher]
After INSERT
AS

Declare @fid int, @PR NVARCHAR(MAX),@Mycounter  as INT
Select top 1 @fid = eid from human where TypeID = 2 
order by NewID()

Select top 1 @PR = Pid from [dbo].[Program] Where Depid = 1
order by NewID()

Set @Mycounter =1
While @Mycounter <5

BEGIN
    Insert Into HeadofDep(SessionID,fid,pid,name,createddate)
    Select SessionID, @fid,@PR,NULL,null from INSERTED
    Where eid in (Select eid from human where TypeID = 3)
set @MyCounter = @MyCounter + 1; 
END

HeadofDep当在教师表中插入任何行时，我需要在表中插入1000行。我已经通过应用循环来完成，但是插入HeadofDep表中的所有行都具有相同的值@PR。每行需要不同的内容。还需要sessionid增加。我该如何实现？

Gotqn

只是，增加SessionIDthen并将其他内容放入循环中：

Declare @fid int, @PR NVARCHAR(MAX),@Mycounter  as INT

Set @Mycounter =1
While @Mycounter <5

BEGIN
Select top 1 @fid = eid from human where TypeID = 2 
order by NewID()

Select top 1 @PR = Pid from [dbo].[Program] Where Depid = 1
order by NewID()

    Insert Into HeadofDep(SessionID,fid,pid,name,createddate)
    Select SessionID, @fid,@PR,NULL,null from INSERTED
    Where eid in (Select eid from human where TypeID = 3)
set @MyCounter = @MyCounter + 1; 
END

同样，在触发器中执行这样的LOOP也是不好的，在这种情况下，您可以使用以下命令更改1000个插入：

Insert Into HeadofDep(SessionID,fid,pid,name,createddate)
Select SessionID + N, @fid,@PR,NULL,null 
from INSERTED
CROSS APPLY
(
    SELECT TOP (1000) -1+row_number() over(order by t1.number) as N
    FROM master..spt_values t1 
        CROSS JOIN master..spt_values t2
) DS 
Where eid in (Select eid from human where TypeID = 3)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。