将表 rrecordset 传递给 SQL 服务器

Question

专家们下午好！

我遇到了与平时非常不同的问题。过去，我通过"传递查询"将一行传递给服务器，有时当我需要传递多个记录时，我利用循环函数将数据多次发送到服务器。但是，如果我有超过 40 行记录，则该循环将需要相当长的时间才能完成。我只是想知道是否有办法使用循环将表集发送到服务器 1 次移动而不是 X 次移动。

这是我在连接到表单中按钮的访问端使用的代码(记录源是一个本地访问表(：

Dim db As dao.Database
Dim rs As dao.Recordset
Dim qdf As dao.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set rs = Me.Recordset
rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF
Set qdf = db.QueryDefs("Qry_Send_ClientData") 'local pass through query
strSQL = "EXEC dbo.SP_Client_Referral @JunctionID='" & Me.ClientID & "', @Note='" & Replace(Me.Txt_Note, "'", "") & "', @Value1='" & Txt_Value1 & "', @Value2='" & Txt_Value2 & "'"
qdf.SQL = strSQL
db.Execute "Qry_Send_ClientData"
rs.MoveNext
Loop
Msgbox "All Client Added!", , "Add client"

现在在SQL服务器端，我有以下存储过程(dbo。SP_Client_Referral( 从传递查询接收数据并将代码行插入特定表

    @ClientID AS NVARCHAR(15),
    @Note As NVARCHAR(500),
    @Value1 As NVARCHAR(50),
    @Value2 As NVARCHAR(50)
AS  
BEGIN
    SET NOCOUNT ON;
        BEGIN
        INSERT INTO dbo.Client_Data(ClientID, Note, Value_1, Value_2)
        SELECT @ClientID, @Note, @Value1, @Value2
        END
END

对于单个记录甚至多达 10 条记录，此方法相对较快。但是，随着记录数量的增加，所需的时间可能会很长。如果有一种方法可以将表(即使用LocalTable中的SELECT *访问端(传递给SQL服务器，而不是逐行传递肯定会节省大量时间。只是想知道这种方法是否存在，如果存在，我将如何发送表以及我必须在 SP 中的 SQL 服务器端使用什么来接收表记录。或者，我可能必须继续使用这种单行方法，并可能使其更有效率，以便执行速度更快。

提前感谢您的帮助！

Answer 1

实际上，最快的方法是什么？

好吧，这似乎非常违反直觉，我可以给出一个很长的解释来解释为什么。但是，试试这个，你会发现它的运行速度是你现在的 10 倍或更好。事实上，它很可能比你拥有的更接近 100 倍。

我们将假设我们有一个标准的链接故事dbo.Client_Data。链接很可能是Client_Data的，甚至是dbo_Cliet_Data的。

因此，请使用此：

Dim rs      As DAO.Recordset
Dim rsIns   As DAO.Recordset
If Me.Dirty = True Then Me.Dirty = False  ' write any pending data
Set rsIns = CurrentDb.OpenRecordset("dbo_Client_Data", dbOpenDynaset, dbSeeChanges)
Set rs = Me.RecordsetClone
rs.MoveFirst
Do While Not rs.EOF
   With rsIns
      .AddNew
      !ClientID = rs!ClientID
      !Note = Me.Txt_Note
      !Value_1 = Me.Txt_Value1
      !Value_2 = Me.Txt_Value2
      .Update
   End With
   rs.MoveNext
Loop
rsIns.Close
MsgBox "All Client Added!", , "Add client"

请注意上面的一些奖金。我们的代码很干净 - 我们不必担心数据类型，如日期，或你凌乱的报价问题。如果涉及日期，我们再次可以分配而不必担心分隔符。我们还获得了启动注射保护的奖励！

我们还使用了me.RecordSetClone.这不是必须做的。这将有助于性能，但最重要的是当您移动记录指针时，表单记录位置不会尝试跟随。这将消除很多潜在的闪烁。如果该表单上存在当前事件，它还可以消除巨大的问题。

因此，虽然这是一个非常好的主意(recordsetclone(，但这并不是您将在此处看到的巨大性能提升的主要原因。 RecordSetClone 与 me.RecordSet 相同，但您可以"移动"和遍历记录集，而无需跟随主窗体。

因此，实际上，最"基本"的代码方法，以及可以在没有SQL Server的情况下进行访问的代码方法，结果证明是最好的方法。它更少的代码，更少的混乱代码，并将为您省去设置+构建SQL Server存储过程的所有麻烦。您的所有概念都不是必需的，更糟糕的是，它们会导致性能损失。尝试上述概念。

Access 将一次性增加和管理多个插件。与reocrdsets相比，始终使用SQL更新/插入命令的概念和想法是一个非常巨大的城市神话，许多访问开发人员都爱上了它。这不是真的。真正的问题是，如果您可以用一个 SQL 更新语句替换大量单独执行的更新的 VBA 循环，那么是的，您遥遥领先(在某些 VBA 循环上使用一个 SQL 更新(。

但是，如果您必须执行多个操作并且每个操作都在一行上？代替"许多单独的"SQL更新，在这种情况下，(是(绝大多数情况，那么记录集将围绕一大堆单独的更新/插入命令运行循环以实现相同的目标。它甚至没有接近，通过使用上述概念，您可以获得 10 倍甚至 100 倍的性能。

Answer 2

您可以尝试将 XML 数据传递给存储过程。

DECLARE @rowData XML
SELECT @rowData = '<data><record clientid="01" Notes="somenotes" Value1="val1" Value2="val2" /><record clientid="02" Notes="somenotes 2" Value1="val1-2" Value2="val2-2" /></data>'
SELECT X.custom.value('@clientid', 'varchar(max)'),
    X.custom.value('@Notes', 'varchar(max)'),
    X.custom.value('@Value1', 'varchar(max)'),
    X.custom.value('@Value2', 'varchar(max)')
FROM @rowData.nodes('/data/record') X(custom)