使用触发器的单次插入/单次触发在SQL Server表中插入多行 [英] Insert multiple rows in SQL Server table with single INSERT / single triggering of trigger

问题描述

我想要什么

能够使用Python在SQL Server中将多行插入到表中，并且仅在插入所有行后才触发INSTEAD OF INSERT触发器。

背景

我的表上有一个INSTEAD OF INSERT触发器，它对以前看不到的数据执行操作。

通常的操作模式是定期一次插入多行。

当我从python插入(使用pyodbc)时，如果我使用：

cursor.executemany('INSERT INTO table_name VALUE (?, ?, ...)', list_of_lists)

触发器将针对每一行触发--这不是我想要的。

我当前正在使用：

sql = 'INSERT INTO table_name VALUES (...),(...)...;'  # string built with format-strings
cursor.execute(sql)

这在理论上为我打开了SQL注入的大门，但我的环境受到了限制，因此实际上不是问题。
另外，我不知道查询字符串的最大限制是多少。 我尝试使用the method in this post找出极限，但返回的结果为空。所以我不确定是没有限制还是未知。

我一次插入的行数预计不会太多(不超过1-2k行，少量列)。因此，我不认为我会达到极限，但我不想稍后感到惊讶。

然后是BULK INSERT选项和FIRE_TRIGGERS选项，但这需要将我的数据作为文件上载到运行SQL Server的计算机。这是我希望避免的复杂性。

有没有更好的选择，或者我当前的解决方案还行吗？

推荐答案

似乎可以使用表类型参数来执行此操作。我要指出的是，我的Python不是很好，所以我不知道您是否可以在不使用的情况下使用存储过程完成此。

不管怎样，首先让我们创建一个示例表：

CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1),
                            SomeString varchar(10),
                            SomeInt int,
                            SomeDate date,
                            SomeGUID uniqueidentifier);
GO

，然后是一个示例类型。我有意省略SomeGUID：

CREATE TYPE dbo.SomeType AS table (SomeString varchar(10),
                                   SomeInt int,
                                   SomeDate date);

然后，您需要创建一个接受上述TYPE作为参数的过程。在下面的代码中，我为SomeGUID定义了整个数据集的静态值，以演示在对最终结果集中的proc的一次调用中插入所有行：

CREATE OR ALTER PROC dbo.SomeProc @SomeData dbo.SomeType READONLY AS
BEGIN

    DECLARE @UID uniqueidentifier = NEWID();
    
    INSERT INTO dbo.SomeTable (SomeString, SomeInt, SomeDate, SomeGUID)
    SELECT SomeString,
           SomeInt,
           SomeDate,
           @UID
    FROM @SomeData;
END;
GO

现在是蟒蛇。为此，我编写了下面的简单脚本，参考this answer以获得一些帮助。我已经包含了完整的工作解决方案的整个脚本，但显然有些脚本不适用于您的脚本，并且您可能没有使用环境文件：

#!/usr/bin/env python3
#Import the bare minimums for this to work
import pyodbc, os
from dotenv import load_dotenv

#Get the details from my environemental file
load_dotenv()
SQLServer = os.getenv('SQL_SERVER')
SQLDatabase = os.getenv('SQL_DATABASE')
SQLLogin = os.getenv('SQL_LOGIN')
SQLPassword = os.getenv('SQL_PASSWORD')

#Create the full connection string
SQLConnString = 'Driver={ODBC Driver 17 for SQL Server};Server=' + SQLServer + ';Database=' + SQLDatabase + ';UID='+ SQLLogin +';PWD=' + SQLPassword

#Define the data that is going to be inserted into the table.
MyData = [('abc',1,'20200527'),('def',2,'20210527')]
#Turn it into a tuple. explained in https://stackoverflow.com/a/61156422/2029983
params = (MyData,) 

#And then execute the procedure, passing params as the parameters; which is a table
with pyodbc.connect(SQLConnString,timeout=20) as sqlConn:
    with sqlConn.cursor() as cursor:
        cursor.execute('EXEC dbo.SomeProc ?;', params)
        sqlConn.commit()

然后，当我运行SELECT * FROM dbo.SomeTable时，我得到以下数据：

ID	字符串	SomeInt	某个日期	某些GUID
1	ABC	1	2020-05-27	945a3656-54ee-47a2-962c-7a34c7f50635
2	定义	2	2021-05-27	945a3656-54ee-47a2-962c-7a34c7f50635

这篇关于使用触发器的单次插入/单次触发在SQL Server表中插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！