Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-04-27
10
Medium Priority
?
253 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 750 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

730 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