在MS-Access中生成INSERT INTO语句 [英] Generate INSERT INTO statements in MS-Access

问题描述

我喜欢将数据(单条记录)从一个Access数据库导出到另一个国家的另一个数据库.我的想法是，我想每封电子邮件发送一个带有INSERT INTO语句的文本文件，而接收方PC只是执行这些INSERT INTO语句.我已经写了代码来读取和执行这些文本文件中的INSERT INTO语句.

I like to export data (single records) from one Access database to another one in another country. The idea is that I want to send a text file with INSERT INTO statements per email and the receiving PC just executes these INSERT INTO statements. I wrote already the code to read and execute the INSERT INTO statements in these text files.

显然，我必须生成INSERT INTO语句.

Obviously I have to generate the INSERT INTO statements.

这里是一个例子.

我有下表:

Table1
Id                number
PersonName        text
DoB               date, can be empty
NumberOfChildern  number, can be empty

我选择这样的数据:

SELECT Id, PersonName, DoB, NumberOfChildern FROM Table1;

我要生成的是这样的语句:

What I want to generate are statements like this:

INSERT INTO Table1 (Id, PersonName, DoB, NumberOfChildern ) VALUES (1, ‘Peter’, #5-17-1990#, 1)

如果所有字段始终都填写，那么我可以编写一次代码，仅此而已.但是，如果几个字段可能包含数据或可能不包含数据，则会出现问题.

If all fields are always filled in then I could write one time the code and that's it. But there is a problem if a couple of fields might contain data or maybe no data.

以下是上述声明的一些相似但不同的版本:

Here are some similar but different versions of the above statement:

INSERT INTO Table1 (Id, PersonName, DoB, NumberOfChildern ) VALUES (1, ‘Peter’, #5-17-1990#, 1)
INSERT INTO Table1 (Id, PersonName, NumberOfChildern ) VALUES (1, ‘Peter’, 1)
INSERT INTO Table1 (Id, PersonName, DoB ) VALUES (1, ‘Peter’, #5-17-1990#)
INSERT INTO Table1 (Id, PersonName ) VALUES (1, ‘Peter’)

只有两个可以包含NULL值的字段，该语句已经有4个不同的版本，而随着字段的增加，它变得越来越复杂(不是很复杂，但是需要更多工作).

With just two fields which can contain NULL values there are already 4 different versions of this statement and with more fields it becomes more and more complicated (not really complicated but more work).

我考虑在VBA中编写代码，该代码将分析要导出的表和记录以检查使用了哪种类型的字段(即日期)，然后生成上述语句. 我确定我可以做到，但是我想知道是否其他人以前可以做到这一点. 我不想重新发明轮子. 但是搜索生成SQL插入语句"并不是很有效.

I think about writing code in VBA which analyzes the table and the records which I want to export to check which kind of fields are used (i.e. date) and then generate statements like above. I am sure I can do this but I wonder if maybe others did this before. I don't want to reinvent the wheel. But searching for "generate SQL insert statements" is not really efficient.

有什么想法吗?

推荐答案

这是您的幸运日.我已经为SQL Server完成了此操作-在下面进行了一些修改后，它应该可以用于Access SQL.

It's your lucky day. I have done this for SQL Server - with a few modifications done below it should work for Access SQL.

关键是要插入VALUES NULL，如果值为null则不要创建其他语句.

The key is to insert VALUES NULL, not create different statements if values are null.

Access可能不需要SET IDENTITY_INSERT ON/OFF.

The SET IDENTITY_INSERT ON/OFF probably isn't needed for Access.

Gustav发布了一个通用函数，该函数可以替代所有Sqlify/SqlDate等帮助函数，并涵盖更多数据类型.

Gustav has posted a generic function that can replace all Sqlify/SqlDate etc. helper functions and covers more data types.

Public Sub InsertStatementsSql(ByVal sTABLE As String)

    Dim DB As DAO.Database
    Dim TD As DAO.TableDef
    Dim RS As DAO.Recordset
    Dim fld As DAO.Field
    Dim sKpl As String
    Dim sStart As String
    Dim sValues As String
    Dim S As String
    Dim v As Variant
    Dim i As Long
    Dim bIdentity As Boolean

    Set DB = CurrentDb
    Set TD = DB.TableDefs(sTABLE)
    Set RS = DB.OpenRecordset(sTABLE, dbOpenSnapshot)


    ' Check for Autonumber/IDENTITY column
    bIdentity = False
    For i = 0 To TD.Fields.count - 1
        If (TD.Fields(i).Attributes And dbAutoIncrField) > 0 Then
            bIdentity = True
            Exit For
        End If
    Next i

    If bIdentity Then
        sKpl = sKpl & "SET IDENTITY_INSERT " & sTABLE & " ON;" & vbCrLf & vbCrLf
    End If

    ' "INSERT INTO ... VALUES " for every line
    For i = 0 To TD.Fields.count - 1
        sStart = StrAppend(sStart, TD.Fields(i).Name, ", ")
    Next i
    sStart = "INSERT INTO " & sTABLE & " (" & sStart & ") VALUES "

    ' One line per record
    Do While Not RS.EOF
        sValues = ""
        For i = 0 To TD.Fields.count - 1
            v = RS(i)
            If IsNull(v) Then
                S = "NULL"
            Else
                Set fld = TD.Fields(i)
                Select Case fld.Type
                    Case dbText, dbMemo: S = Sqlify(CStr(v))
                    Case dbDate: S = SqlDate(CDate(v))
                    Case dbDouble, dbSingle: S = SqlNumber(CDbl(v))
                    Case Else: S = CStr(v)
                End Select
            End If
            sValues = StrAppend(sValues, S, ", ")
        Next i
        ' Append line to full SQL
        sKpl = sKpl & vbCrLf & sStart & " (" & sValues & ");"
        RS.MoveNext
    Loop
    RS.Close
    Set TD = Nothing

    If bIdentity Then
        sKpl = sKpl & vbCrLf & vbCrLf & "SET IDENTITY_INSERT " & sTABLE & " OFF;" & vbCrLf
    End If

    Debug.Print sKpl

    ' see https://support.microsoft.com/en-us/kb/210216 or https://msdn.microsoft.com/en-us/library/office/ff192913.aspx
    ' or https://stackoverflow.com/a/25431633/3820271
    'ClipBoard_SetData sKpl

End Sub

' ------------------- helper functions -----------------

'  ein'string --> 'ein''string'
Public Function Sqlify(ByVal S As String) As String

    S = Replace(S, "'", "''")
    S = "'" & S & "'"
    Sqlify = S

End Function

Public Function SqlDate(vDate As Date) As String
    SqlDate = "#" & Format(vDate, "yyyy-mm-dd") & "#"
End Function

Public Function SqlNumber(num As Double) As String
    SqlNumber = Replace(CStr(num), ",", ".")
End Function

Public Function StrAppend(sBase As String, sAppend As Variant, sSeparator As String) As String

    If Len(sAppend) > 0 Then
        If sBase = "" Then
            StrAppend = Nz(sAppend, "")
        Else
            StrAppend = sBase & sSeparator & Nz(sAppend, "")
        End If
    Else
        StrAppend = sBase
    End If

End Function

这篇关于在MS-Access中生成INSERT INTO语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！