Generating INSERT statements for a table

[mendhak]

Does SQL server have an inbuilt SP which I could use to generate INSERT INTO...VALUES(...) statements for all the data the table contains?

[Blade]

Not that I'm aware of.
You could write one using the SysObjects and SysColumns tables I would have thought. Might save time in the long run.

[szlamany]

Does SQL server have an inbuilt SP which I could use to generate INSERT INTO...VALUES(...) statements for all the data the table contains?

Not sure I really understand your question - but...

If you go into QUERY ANALYZER - and have the object panel open - expand the tree until you see the table you want...

Then RIGHT-CLICK and DRAG that table into the QUERY PANEL - you will get a pop-up menu with options...

Choose INSERT and you will get an INSERT template.

What do you want to do with this anyway?

[Blade]

I understood it to be that he wants to generate a list of inserts that he can use to port data from a table to a copy of itself some where (but can't use DTS).
ie. He has Table1 with cols Col1 and Col2.

--data
Col1 Col2
'AAA' 111
'BBB' 222
'CCC' 333

and from that he wants to generate a text file of inserts like:

INSERT INTO Table1 (Col1, Col2) VALUES ('AAA', 111)
INSERT INTO Table1 (Col1, Col2) VALUES ('BBB', 222)
INSERT INTO Table1 (Col1, Col2) VALUES ('CCC', 333)

[szlamany]

That seems like an expensive way to move data.

We have a little program called TEXTOUTPUT that creates a TDF text file from any table we want - tables identified in a TEXTOUTPUT.INI file...

Those TDF text files load back into SQL effortlessly with BULK INSERT (from QA) or BCP (which we never have actually used).

Nice thing is that it's not a "logged" activity...

[mendhak]

The reason I want to do this, even though it's expensive, is because I need to insert seed data into certain tables for my deployment. I wanted to simply run query analyzer for that, but had to find a convenient way to generate the INSERT statements.

This is an SP I found on the Internet, but for tables with lots of columns, it just falls over it's own rear end:

CREATE PROCEDURE GetInsertStatements
@TABLENAME VARCHAR ( 2000 )
AS
BEGIN

DECLARE CUR_METADATA CURSOR FOR
SELECT NAME , XTYPE
FROM SYSCOLUMNS
WHERE ID IN ( SELECT ID FROM SYSOBJECTS WHERE NAME = @TABLENAME )
/* DECLARE VARIABLES*/
DECLARE @QRY1 VARCHAR (8000 ) /* Has column names */
DECLARE @QRY2 VARCHAR ( 8000 ) /* Has column values */

DECLARE @COLNAME VARCHAR ( 8000)
DECLARE @COLDATATYPE TINYINT
DECLARE @PRVCOLDATATYPE TINYINT

DECLARE @CHARPREFIX VARCHAR ( 5 )
DECLARE @CHARPOSTFIX VARCHAR ( 5 )
DECLARE @COLUMNSEPARATOR CHAR ( 1 )
DECLARE @NULLCHAR CHAR ( 4 )

SET @CHARPREFIX = ''''
SET @CHARPOSTFIX = ''''
SET @COLUMNSEPARATOR = ','
SET @NULLCHAR = 'NULL'

/* Start building the query */
SET @QRY1 = "SELECT 'INSERT INTO " + @TABLENAME + "(" --+ " VALUES ( "

SET @QRY2 = ''

OPEN CUR_METADATA

FETCH NEXT FROM CUR_METADATA INTO @COLNAME , @COLDATATYPE

WHILE @@FETCH_STATUS = 0
BEGIN
/* Add the Column names to the query */
SET @QRY1 = @QRY1 + @COLNAME + ','

/* Add Column values - Prefix & postfix the column values with quotes if they belong to any of the following datatypes */
/* TEXT - 35 | SMALLDATETIME - 58 | DATETIME - 61 | NTEXT - 99 | VARCHAR - 167 | CHAR - 175 | NVARCHAR - 231 | NCHAR - 239  */
IF ( @COLDATATYPE = 35 ) OR ( @COLDATATYPE = 58 ) OR ( @COLDATATYPE = 61 ) OR ( @COLDATATYPE = 99 ) OR
( @COLDATATYPE = 167 ) OR ( @COLDATATYPE = 175 ) OR ( @COLDATATYPE = 231 ) OR ( @COLDATATYPE = 239 )
BEGIN
IF @QRY2 = ''
BEGIN
SET @QRY2 = @QRY2  + @CHARPREFIX  + @CHARPREFIX  + @CHARPREFIX + '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' +  "'NULL'" + ')' --+ "'')"
END
ELSE
BEGIN
SET @QRY2 = @QRY2   + @CHARPREFIX  + @CHARPREFIX  + @CHARPREFIX +  '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' +  "'NULL'" + ')' --+ "'')"
END
SET @QRY2 = @QRY2 + '+' + @CHARPOSTFIX + @CHARPOSTFIX + @CHARPOSTFIX + @COLUMNSEPARATOR
END
ELSE /*If the column is a number dont prefix/postfix quotes */
BEGIN
SET @QRY2 = @QRY2  + '''' +  '+ISNULL(CAST(' + @COLNAME + ' AS VARCHAR),' +  "'NULL'" + ')'
SET @QRY2 = @QRY2 + '+'  + @CHARPOSTFIX + @COLUMNSEPARATOR
END
--PRINT @QRY2
SET @PRVCOLDATATYPE =  @COLDATATYPE
FETCH NEXT FROM CUR_METADATA INTO @COLNAME , @COLDATATYPE
END

CLOSE CUR_METADATA
DEALLOCATE CUR_METADATA

SET @QRY1 = SUBSTRING ( @QRY1 , 1 , LEN ( @QRY1 ) - 1 )
SET @QRY1 = @QRY1 + ')VALUES('  --+ "'" + "'" +"'"

SET @QRY2 = SUBSTRING ( @QRY2 , 1 , LEN ( @QRY2 )  - 1 )
SET @QRY2 = @QRY2 + ')' + @CHARPOSTFIX
SET @QRY2 = @QRY2 + ' FROM ' + @TABLENAME

/* Create a temporary table to hold the result of the query - i.e. it will have the INSERT statements */
CREATE TABLE #SQLTRACE ( SQL VARCHAR ( 8000 ) )
--print  @QRY1 + @QRY2
SET @QRY2 =  ' INSERT INTO #SQLTRACE '  + @QRY1 + @QRY2
IF LEN ( @QRY2 )  < 8000
BEGIN
SET NOCOUNT ON
--PRINT @QRY2
EXEC ( @QRY2 )
--PRINT  ' INSERT INTO #SQLTRACE '  + @QRY1 + @QRY2
--EXEC ( ' INSERT INTO #SQLTRACE '  + @QRY1 + @QRY2 )
UPDATE #SQLTRACE SET SQL = REPLACE ( SQL , "'NULL'" , @NULLCHAR )
SELECT * FROM #SQLTRACE
END
ELSE
BEGIN
PRINT 'UNABLE TO GENERATE INSERT STATEMENTS FOR TABLE :' +@TABLENAME
END
SET NOCOUNT OFF
END /* END OF FILE */
GO

[Blade]

Nice stuff. Had started on something similar but only got as far as the column list

Above query works fine on most tables I tried on except for one which goes over the 8096 row size (which needs changing anyway ) Not sure what you can do about tables that have data over 8k per row (inc. col names) as that's the max for the varchar can hold.

[mendhak]

Try it on a table with 30 columns.

[Blade]

I tried it with one that had 42 columns and it was fine.
It's not the number of columns that's the problem, I'm sure it's the actual size of the data in the columns that's creating an insert text larger than 8k (ie. the size of the varchar that's holding it).

[szlamany]

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.

[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