ssis从另一个表更新表值

让我们先清理一下:

Public Sub Main()

    Const Component_Name As String = "Updating TableA Data"
    Const Conn_String As String = "Data Source=DB_A;Initial Catalog=TableA;Integrated Security=SSPI;"

    Const sql_Emp As String = _
            "UPDATE TableA" _ 
             + " SET Contract = c.License_No, SEIN = convert(varchar, c.Lic_Exp_Date, 101)" _
         + " FROM Server.DB_B.dbo.TableB c" _
         + " INNER JOIN TableA b" _
             + " ON rtrim(ltrim(c.business_lic)) = rtrim(ltrim(cast(b.Account_Key as varchar(14))))" _
         + " WHERE c.Lic_Exp_Date= (" _
             + " SELECT MAX(Lic_Exp_Date)" _
             + " FROM Server.DB_B.dbo.TableB" _
             + " WHERE rtrim(ltrim(business_lic)) = rtrim(ltrim(cast(b.Account_Key as varchar(14))))" _
            + ") AND convert(varchar, c.Lic_Exp_Date, 101) <> convert(varchar, b.SEIN, 101)"

    Try
        Using con As New SqlConnection(Conn_String), _
              cmds New SqlCommand(sql_Emp, con)

            Dts.Log("Opening DB Connection: " & con.ConnectionString, 0, Nothing)

            con.Open()
            cmd.ExecuteNonQuery()

            Dts.Log(String.Format(Component_Name), 0, Nothing)
            Dts.Events.FireInformation(0, Component_Name, String.Format("TableA Data Updating"), "", 0, True)
            Dts.TaskResult = Dts.Results.Success
        End Using

    Catch ex As Exception
        Dts.Events.FireError(0, Component_Name, ex.Message, "", 0)
        Dts.Log("Exception detected: " & ex.ToString, 0, Nothing)
        Dts.TaskResult = Results.Failure

    End Try

End Sub

好吧,现在我可以读了,我可以开始看什么可能会被打破。几分钟后返回查看以进行编辑。

好的,现在让我们看看这个问题。我缺少一些数据类型信息,所以我要做一些假设。请纠正任何错误:

• b.account_key是某种数字类型,可能是int,否则不需要转换为varchar
• lic ou exp ou date列实际上是datetime类型

如果这些都是正确的,我想这会满足你的需要,但是做一个 许多 更快:

UPDATE TableA
    SET Contract = c1.License_No, SEIN = DATEADD(dd,0, DATEDIFF(dd,0, c1.Lic_Exp_Date))
 FROM TableA b
 INNER JOIN Server.DB_B.dbo.TableB c1
     ON ISNUMERIC(c1.busines_lic) = 1 AND cast(c1.business_lic AS int) = b.Account_Key
 INNER JOIN 
     (
        SELECT business_lic, MAX(Lic_Exp_Date) AS Lic_Exp_Date 
        FROM Server.DB_B.dbo.TableB
        GROUP BY business_lic, License_No
     ) c2 ON c2.business_lic = c1.business_lic AND c1.Lic_Exp_Date=c2.Lic_Exp_Date
 WHERE DATEADD(dd,0, DATEDIFF(dd,0, c1.Lic_Exp_Date)) <> DATEADD(dd,0, DATEDIFF(dd,0, b.SEIN))

以下是改变的:

• 将相关的子查询转换为联接。一个连接会快得多,但是查询优化器可能已经为您做了这些。
• 无需调用多个每行函数——这也有助于更好地匹配索引。
• 对主联接使用整数比较而不是字符串
• 使用日期函数而不是转换为字符串来删除时间部分,该部分应该快得多,从而允许我:
• 在联接中使用日期比较而不是字符串比较

更好的方法是执行SQL任务。