使用select和procedure批量插入 - SQL Server

I've been googled about my problem, but I didn't find anything relevant.

我一直在谷歌搜索我的问题，但我没有找到任何相关的东西。

I'm executing a Bulk Insert with flag FIRE_TRIGGERS enabled.

我正在执行一个启用了标志FIRE_TRIGGERS的批量插入。

So, I need pass each row in my Bulk Insert to a procedure.

所以，我需要将批量插入中的每一行传递给一个过程。

When I execute a insert row by row, my triggers works fine but when I execute a Bulk Insert my triggers don't work.

当我逐行执行插入时，我的触发器工作正常，但是当我执行批量插入时，我的触发器不起作用。

My trigger code:

我的触发代码：

ALTER TRIGGER [dbo].[tgSetDetails]
   ON  [PORTALSQLDB].[dbo].[BurnTimeRawData]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @ServiceTag varchar(10);
    DECLARE @Platform varchar(50);

    SELECT
        @ServiceTag = ServiceTag,
        @Platform = Platform
    FROM
        inserted

    EXEC spBurnTimeInsertData @ServiceTag, @Platform
END

Any idea how I can do this job?

知道我怎么能做这份工作吗？

Thanks :)

谢谢：）

1 个解决方案

#1

This trigger will fire once for each insert statement or bulk insert batch. To call a proc for each row, you'll need a cursor.

对于每个插入语句或批量插入批处理，此触发器将触发一次。要为每一行调用proc，您需要一个游标。

It would be better to refactor the proc code as a set-based operation to include in the trigger code. That will perform much better.

最好将proc代码重构为基于集合的操作以包含在触发器代码中。那将会表现得更好。

#1