Solved

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

Posted on 2004-04-27
10
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

630 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