Dynamic INSERT statements don't know wheather to use quotes or not

How are you able to "...create the insert statement dynamically"?

Have you considered using DTS to load the data from the Excel spreadsheets into your tables?

1) Right-Click on your database in Enterprise Manager
2) Select All Tasks --> Import Data
3) Follow the Wizard...

~Brian

As he bwdowhan said, If you want to do it in a manual manner you can considering using Import and Export Data (DTS) Provided by Microsoft Sql Server Data Transfer tool.

if you want to automate the process, In Excel in any one given column that can have only one type of data. Otherwise those that comes with Integer in a varchar column will be considered as Null. So you can ensure that by adding single quote ( ' ) in the begining and everything will bcome string n makes ur life easier for inserting also.

Rgds
Sara

Below is some VB code in Excel that creates the INSERT statement. It loops through
several spreadsheets, and that's how the statement is created dynamically. For
all the columns in all the spreadsheets, I don't know what type it is in the database,
so I always use the quotes around the value (see line commented with "QUOTES").

Is there any way that the value '28' could be interpreted as just 28 ? This has
worked for me before with "int" and "bit", but not for "numeric".

------
Sub insert_data(file As String, con As ADODB.Connection, numOfTables, arrTables)
    Dim currentWorkbook As Excel.Workbook
    Set currentWorkbook = Workbooks.Open(file)

    On Error GoTo Continue:
    For dbTable = 0 To (numOfTables - 1)
        Set currentSheet = currentWorkbook.Sheets(arrTables(dbTable, 0))
        For rowCnt = 2 To 65536
            insertStr = getInsertStr(currentSheet, rowCnt, arrTables(dbTable, 0), arrTables(dbTable, 1))
            If (insertStr = "") Then
                Exit For
            Else
                    con.Execute (insertStr)
            End If
Continue:
        Next rowCnt
    Next dbTable
    currentWorkbook.Close
End Sub

Function getInsertStr(currentSheet, currentRow, tableName, numOfColumns) As String
    insertStr = "INSERT INTO " & tableName & " VALUES("
    For column = 1 To numOfColumns
        aValue = Trim(currentSheet.Range(Chr(64 + column) & currentRow).Value)
        If (column = 1) Then
            If Len(aValue) = 0 Then
                insertStr = ""
                Exit For
            End If
        End If
        insertStr = insertStr & "'" & aValue & "'"  ' QUOTES
        If (column = numOfColumns) Then
            insertStr = insertStr & ")"
        Else
            insertStr = insertStr & ","
        End If
    Next column
    getInsertStr = insertStr
End Function

Could you add a condition to the line that defines the field with quotes and just use the aValue if it is Numeric? Something like:

If IsNumeric(aValue) Then
  insertStr = insertStr & aValue
Else
  insertStr = insertStr & "'" & aValue & "'"  ' QUOTES
End If

~Brian

Brian: That would be nice. The problem is that there are columns of type char
and varchar that can have only numbers.

So the question would be, does the implicit conversion from int to varchar work?

INSERT INTO Table1 (vcName) VALUES (1000)

I guess the the easiest way is to define all columns as either char or varchar ..
.. but as it has worked before (world's most used expression ;)) there could be
some setting, driver configuration, version of SQL Server that is the cause of this?

It is not mandatory that you put quotes around values that you insert into a char or varchar field

Any of these should work perfectly fine:
INSERT INTO Table1 (vcName) VALUES(100000)
INSERT INTO Table1 (vcName) VALUES('100000')
INSERT INTO Table1 (vcName) VALUES("100000")

Interesting. Why did it not work for me ?