Solved

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

Posted on 2004-04-27
10
199 Views
Last Modified: 2008-03-06
I want to make a bulk insert of data from Excel sheets. When I read from Excel I dont know
what type it is in the database, which means I don't know wheather to have quotes around
the value or not.

I create the insert statement dynamically, and it has worked for me before to always use
quotes, even around int and bit, and the data was interpreted correctly.

However it doesn't work for me now, and I have no clue how to make it work other that
to define all fields as varchar instead of int and bit.

In short, I want this to work:
INSERT INTO Table1 (vcName, inAge) VALUES ('Tobbe', '28')

Thanks for your help,
Tobbe
0
Comment
Question by:TobbeF
10 Comments
 
LVL 3

Expert Comment

by:Frostbyte_Zero
ID: 10933272
How are you able to "...create the insert statement dynamically"?
0
 
LVL 5

Expert Comment

by:bwdowhan
ID: 10933711
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
0
 
LVL 4

Expert Comment

by:kssaran
ID: 10934623
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
0
 

Author Comment

by:TobbeF
ID: 10936232
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
0
 
LVL 5

Expert Comment

by:bwdowhan
ID: 10936380
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:TobbeF
ID: 10936730
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)
0
 

Author Comment

by:TobbeF
ID: 10936749
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?
0
 
LVL 1

Expert Comment

by:iyerbhuvanac
ID: 10937390
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")

0
 

Author Comment

by:TobbeF
ID: 10937642
Interesting. Why did it not work for me ?
0
 
LVL 5

Accepted Solution

by:
bwdowhan earned 250 total points
ID: 10940659
Tobbe,

SQL Will perform an implicit conversion from int to varchar and vice versa. When I setup a table with two columns:

create table Table 1
(
  vcName varchar(20),
  intAge   int  --I even set this to numeric with the same results
)

and then insert values:

insert into Table1 (vcName, intAge) Values ('Tobbe','28')
or
insert into Table1 (vcName, intAge) Values ('Tobbe',28)

They both work fine...

Can you post the following information to help narrow down this problem:

1) structure of the table  (sp_help Table1). I know you won't have this information from Excel but if you can get it for testing it might help
2) sample insert statements that were actually dynamically generated from your Subroutines
3) The actual error message you are getting when the process failes


~Brian
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now