Generating INSERT statements for a table

[mendhak]

We seed data in our deployed systems with .SQL scripts like this - that run in QA or with ISQL/OSQL (can never remember which one!)...

USE Acctfiles
GO
delete from ledger_t
delete from invoice_t
delete from po_t
.
.
.
delete from cycle_t
delete from batch_t
delete from vendor_t
Go
PRINT 'About to Insert Invoice_T_0'
BULK INSERT Invoice_T FROM "c:\acs desktop\textoutput acct\Invoice_T_0.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Po_T_0'
BULK INSERT Po_T FROM "c:\acs desktop\textoutput acct\Po_T_0.txt" WITH
(KEEPIDENTITY
)
GO

BULK INSERT ShipTo_T FROM "c:\acs desktop\acctfiles\ShipTo_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT GLFund_T FROM "c:\acs desktop\acctfiles\GLFund_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT GLObject_T FROM "c:\acs desktop\acctfiles\GLObject_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT GLSubObj_T FROM "c:\acs desktop\acctfiles\GLSubObj_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLType_T FROM "c:\acs desktop\acctfiles\SLType_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLFund_T FROM "c:\acs desktop\acctfiles\SLFund_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLRcvType_T FROM "c:\acs desktop\acctfiles\SLRcvType_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLOrg_T FROM "c:\acs desktop\acctfiles\SLOrg_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLFunc_T FROM "c:\acs desktop\acctfiles\SLFunc_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLObj_T FROM "c:\acs desktop\acctfiles\SLObj_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLSubObj_T FROM "c:\acs desktop\acctfiles\SLSubObj_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLSchObj_T FROM "c:\acs desktop\acctfiles\SLSchObj_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLSchProgram_T FROM "c:\acs desktop\acctfiles\SLSchProgram_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLSchDept_T FROM "c:\acs desktop\acctfiles\SLSchDept_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLASN_T FROM "c:\acs desktop\acctfiles\SLASN_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT SLYear_T FROM "c:\acs desktop\acctfiles\SLYear_T.txt" WITH
(KEEPIDENTITY
)
GO
BULK INSERT GLAcct_T FROM "c:\acs desktop\acctfiles\GLAcct_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Control_T'
BULK INSERT Control_T FROM "c:\acs desktop\acctfiles\Control_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Cycle_T'
BULK INSERT Cycle_T FROM "c:\acs desktop\acctfiles\Cycle_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Batch_T'
BULK INSERT Batch_T FROM "c:\acs desktop\acctfiles\Batch_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Vendor_T'
BULK INSERT Vendor_T FROM "c:\acs desktop\acctfiles\Vendor_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Check_T'
BULK INSERT Check_T FROM "c:\acs desktop\acctfiles\Check_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Invoice_T'
BULK INSERT Invoice_T FROM "c:\acs desktop\acctfiles\Invoice_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Po_T'
BULK INSERT Po_T FROM "c:\acs desktop\acctfiles\Po_T.txt" WITH
(KEEPIDENTITY
)
GO
PRINT 'About to Insert Ledger_T'
BULK INSERT Ledger_T FROM "c:\acs desktop\acctfiles\Ledger_T.txt" WITH
(KEEPIDENTITY
)
GO

Mendhak - I'm still unclear on why you need to build INSERT statements to insert data into tables that you know the structure of in advance.

Ah, I see what you're doing in your code, and that had not occurred to me.

I wanted to generate INSERT statements like that because I didn't really want to type out the entire INSERT statement of a say, 60 column table.

Your method actually looks pretty good though.

[szlamany]

Like I said in an earlier post - we have this little program call TEXTOUTPUT...

This is a SUBMAIN from within that program (excuse the extremely poor code - probably the second program I wrote in VB four years ago when we migrated from the mainframes )...

Public Sub Main()

Dim i As Long, j As Long, k As Long, x As Long, y As Long, z As Long
Dim s1 As String, s2 As String, s3 As String, s4 As String, s5 As String

Dim booIni As Boolean _
    , booSysStatOff As Boolean _
    , booConnect As Boolean _
    , booCleanup As Boolean
Dim rs As New ADODB.Recordset
Dim strDate As String
Dim strSel As String
Dim strTab As String
Dim lngDateFields() As Long
Dim f As Field
Dim strTbl(1 To 50) As String
Dim strSQL(1 To 50) As String
Dim lngCount(1 To 50) As Long
Dim lngTblCnt As Long
Dim booNoTDATE As Boolean
Dim booPosPay As Boolean

Dim strCheckType As String
Dim strCheckNum As String
Dim strCheckAmt As String
Dim strCheckStart As String
Dim strCheckEnd As String
Dim strIssueDate As String
Dim lngVoidCount As Long
Dim curVoidAmt As Currency
Dim curCheckAmt As Currency
Dim lngIssueCount As Long
Dim curIssueAmt As Currency

Dim objcmdSP As New ADODB.Command

Debug.Print "Main"
On Error GoTo Error_Handler

Begin:
    'MousePointer = vbHourglass

    If App.PrevInstance = True Then End
    
    Open App.Path & "\" & App.EXEName & ".ini" For Input Access Read Shared As #1
    
    gstrOutput = App.Path & "\"
    
    Do While Not EOF(1)
        Line Input #1, s1
        i = InStr(s1, "=")
        If i Then
            Select Case Left(s1, i - 1)
                Case "SERVER"
                    gstrServer = Mid(s1, i + 1)
                Case "DATABASE"
                    gstrDatabase = Mid(s1, i + 1)
                Case "TABLE"
                    lngTblCnt = lngTblCnt + 1
                    strTbl(lngTblCnt) = Mid(s1, i + 1)
                    strSQL(lngTblCnt) = "Select * from " & strTbl(lngTblCnt)
                Case "SELECT"
                    strSQL(lngTblCnt) = Mid(s1, i + 1)
                Case "COUNT"
                    lngCount(lngTblCnt) = CLng(Mid(s1, i + 1))
                Case "OUTPUT"
                    If Right(s1, 1) <> "\" Then s1 = s1 & "\"
                    gstrOutput = Mid(s1, i + 1)
                Case "TDATE"
                    If Mid(s1, i + 1) = "NO" Then booNoTDATE = True
                Case "SP"
                    gstrSP = Mid(s1, i + 1)
                Case "POSPAY"
                    booPosPay = True
            End Select
        End If
    Loop
    
    Close #1
    
    Open gstrOutput & "TextOutput.log" For Output As #2
    Print #2, "Log File Created"
    
    gstrProgram = "Text Output Application"
    
    booConnect = False
    booCleanup = True
    booSysStatOff = False

    'Establish connection to SQL Server
    If Not EstablishConnection(gstrServer, gstrDatabase) Then
        Print #2, "Cannot Establish Connection"
        GoTo Abort_Error
    End If

    Print #2, "Connection established - about to create ConnectRecord"

    booConnect = True
    
    Call ConnectRecord
    
    If gintConnId = 0 Then
        Print #2, "Connection Id=0 - not successful"
        End
    End If
    
    For i = 1 To lngTblCnt
        Open gstrOutput & strTbl(i) & ".txt" For Output As #1
        strSel = strSQL(i) ' "Select * From " & strTbl(i)
        rs.Open strSel, gCn
        
        ReDim lngDateFields(0 To rs.Fields.Count - 1)
        
        If lngCount(i) = 0 Then
            lngCount(i) = rs.Fields.Count - 1
        Else
            lngCount(i) = lngCount(i) - 1
        End If
        
        For j = 0 To lngCount(i)
            ' 135 - Date, 3 - int, 129 - char
            Select Case rs.Fields(j).Type
                Case 135
                    lngDateFields(j) = 1
            End Select
        Next j
            
        Do While Not rs.EOF
            glngRecAffected = glngRecAffected + 1
            s2 = ""
            strTab = ""
            For j = 0 To lngCount(i)
                If booNoTDATE = True And j = lngCount(i) Then
                    s2 = s2 & strTab
                Else
                    If lngDateFields(j) = 1 Then
                        s2 = s2 & strTab & Format(rs.Fields(j), "yyyymmdd")
                    Else
                        s2 = s2 & strTab & rs.Fields(j)
                    End If
                End If
                If j = 0 Then strTab = vbTab
            Next j
            Print #1, s2
            
            If booPosPay Then
                If Left(s2, 2) <> "$$" Then
                    strCheckNum = Left$(s2, 10)
                    strCheckAmt = Mid$(s2, 11, 11)
                    strCheckType = Mid$(s2, 44, 1)
                    curCheckAmt = CCur(Left$(strCheckAmt, 9) & "." & Mid$(strCheckAmt, 10))
                    If strCheckType = "V" Then
                        lngVoidCount = lngVoidCount + 1
                        curVoidAmt = curVoidAmt + curCheckAmt
                    Else
                        If strIssueDate = "" Then
                            strIssueDate = Mid$(s2, 22, 6)
                            strIssueDate = Mid$(strIssueDate, 3, 2) & "/" & Mid$(strIssueDate, 5, 2) & "/" & Left(strIssueDate, 2)
                        End If
                        lngIssueCount = lngIssueCount + 1
                        curIssueAmt = curIssueAmt + curCheckAmt
                        If strCheckStart = "" Or strCheckNum < strCheckStart Then strCheckStart = strCheckNum
                        If strCheckEnd = "" Or strCheckNum > strCheckEnd Then strCheckEnd = strCheckNum
                    End If
                End If
            End If
            
            rs.MoveNext
        Loop
        
        rs.Close
 
        Close #1
    Next i
    
    Print #2, ""
    Print #2, "Records Affected : "; glngRecAffected

    If gstrSP <> "" Then
        Print #2, ""
        Print #2, "Executing SP="; gstrSP
        objcmdSP.ActiveConnection = gCn
        objcmdSP.CommandText = gstrSP
        objcmdSP.CommandType = adCmdStoredProc
        objcmdSP.CommandTimeout = 300

        objcmdSP.Execute
    End If
        
    If booPosPay Then
        Print #2, ""
        Print #2, "Issue Date                     : "; strIssueDate
        Print #2, "Total Number of Issues         : "; lngIssueCount
        Print #2, "Total Dollar Amount            : "; FormatNumber(curIssueAmt)
        Print #2, "Beginning Serial Number        : "; strCheckStart
        Print #2, "Ending Serial Number           : "; strCheckEnd
        Print #2, ""
        Print #2, "Total Number of Voids          : "; lngVoidCount
        Print #2, "Total Dollar Amount (Voids)    : "; FormatNumber(curVoidAmt)
    End If
        
    Call ConnectRecord
    Call TerminateConnection

    'MsgBox "Success"

It's all controlled through a .INI file in the app directory. The program is designed to run in a .BAT file - so no user interface...

Here's an example of the .INI file. Note that if the SELECT= statement is left off, the default is SELECT * from the TABLE listed. We keep adding new keyword controls to the .INI file - last we did was for a POSITIVE PAY summary sheet for a BANK TRANSMISSION file.

SERVER=SZLAMANYNB
DATABASE=Acctfiles
TABLE=PO_T
SELECT=Select * from PO_T where FiscalYr<>0 and POEntry=0
TABLE=Invoice_T
SELECT=Select * from Invoice_T where FiscalYr<>0 and InvEntry=0
OUTPUT=c:\acs desktop\TextOutput Acct