sql - 将 Null 从 Excel 传递到 SQL

Question

我正在使用 VBA 脚本来更新 SQL 中的表，到目前为止它工作得很好。问题出在处理空值时。由于 VBA 脚本循环直到第一列结束，因此工作表中的某些值为空，但它们在 sql 中被放置为零。

在 Excel 中:

    Name     Last name      Age    
    daniel   mtz            22
    jose     mtz            25
    John     doe    

最终的SQL结果:

    Name     Last name      Age    
    daniel   mtz            22
    jose     mtz            25
    John     doe            0

关于如何告诉宏或 SQL 将它们读取为空而不是零，有什么想法吗？

P.S，对 sql 中的每一列进行编程以将 null 放入相应的值中不是一个选项，因为该表会不断变化，我可以填充所有列，或者只是填充一些列，等等。

[更新] 这是 VBA 的代码:

Sub Macro_CargarSQL()

'VARIABLES
Dim conn As New ADODB.Connection
Dim iRowNo As Integer
'Column variables
Dim sname, slastname, sage As String

'CONECTION TO SERVER Y BD
conn.Open "Provider=SQLOLEDB;Data Source=Termxgesvsql07\vfsitesamn;Initial Catalog=DW_PLCO;Integrated Security=SSPI;"
'Import into SQL
With Sheets("test")
    'Start row 2
    iRowNo = 2
    'Delete previous
    conn.Execute "delete from table"

    'Loop for insert
    Do Until .Cells(iRowNo, 1) = ""
        sname = .Cells(iRowNo, 1)
        slastname = .Cells(iRowNo, 2)
        sage = .Cells(iRowNo, 3)

        conn.Execute "insert into dbo.table (name, lastname,age ) values ('" & sname & "', '" & slastname & "', '" & sage & "')"
        iRowNo = iRowNo + 1
    Loop
End With
conn.Close
Set conn = Nothing

End Sub

Answer 1

我这样做的一种方法是创建两个 SQL 字符串并检查实际值。

您可以按如下方式执行此操作(通常，如果您有一个数组，这会容易得多，因为您可以通过值/字段名编写迭代)。

Do Until .Cells(iRowNo, 1) = ""
    sName = .Cells(iRowNo, 1)
    slastname = .Cells(iRowNo, 2)
    sage = .Cells(iRowNo, 3)

    Dim sqlStrStart As String
    Dim sqlStrValues As String
    Dim sqlStrFull As String

    sqlStrStart = "insert into dbo.table ("
    sqlStrValues = " values ("

    'build string dynamically (generally I do this using parameters, but this will work)
    If sName <> "" Then
        sqlStrStart = sqlStrStart + "name,"
        sqlStrValues = sqlStrValues + sName & ","
    End If
    If slastname <> "" Then
        sqlStrStart = sqlStrStart + "lastname,"
        sqlStrValues = sqlStrValues + slastname & ","
    End If
    If sName <> "" Then
        sqlStrStart = sqlStrStart + "age,"
        sqlStrValues = sqlStrValues + sage & ","
    End If

    'remove last comma
    sqlStrStart = Left(sqlStrStart, Len(sqlStrStart) - 1)
    sqlStrValues = Left(sqlStrValues, Len(sqlStrValues) - 1)

    'close parenthesis
    sqlStrStart = sqlStrStart & ")"
    sqlStrValues = sqlStrValues & ")"

    'combine
    sqlStrFull = sqlStrStart + sqlStrValues + ";"


    conn.Execute sqlStrFull
    iRowNo = iRowNo + 1
Loop