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

x
?
Solved

SQL Server Insert Query - single quote problem

Posted on 2009-04-09
13
Medium Priority
?
907 Views
Last Modified: 2012-05-06
Please review the following lines and tell me your suggestions

INSERT INTO tbl_test(fld_Id,fld_name,fld_interests)
VALUES('1','Andy','A1','A3','A4')

column values described as below

fld_id - 1
fld_name - Andy
fld_interests - 'A1','A3','A4'

when executing this query im facing error says
"no of columns and values not matched"

i need to insert single quote separated values into a single column
how can i do this

It is possible by using escape sequence, but i need to add and remove single quotes dynamically

is there any other suggestions

please advice
0
Comment
Question by:nissiinfotechpvtltd
[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
  • 4
  • 4
  • 3
  • +1
13 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24105039
can u not use double quotes?
0
 
LVL 23

Expert Comment

by:adathelad
ID: 24105060
As you're trying to store multiple values in a CSV form into a single field, I'd recommend you consider a different approach - have a seperate tbl_test_interest table with fields:
fld_id
fld_interest

This would then have a 1-many relationship with the tbl_test table so that a record in tbl_test (e.g. '1', 'Andy') can have multiple related records in the tbl_test_interest table, one record in there for each interest e.g.
fld_id    fld_interest
1           A1
1           A3
1           A4


Otherwise, back to your actual question you need to double up your quotes:
INSERT INTO tbl_test(fld_Id,fld_name,fld_interests)
VALUES('1','Andy','''A1'',''A3'',''A4''')
0
 
LVL 11

Expert Comment

by:bmatumbura
ID: 24105137

INSERT INTO tbl_test(fld_Id,fld_name,fld_interests)
VALUES('1','Andy','''A1'',''A3'',''A4''')

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 11

Expert Comment

by:bmatumbura
ID: 24105151
You must replace every single quote in your source string with double single quotes ('') . This will be stored as a single quote in the database
0
 

Author Comment

by:nissiinfotechpvtltd
ID: 24105177
hai adathelad:

i already considered the 1-many relationship
but it not just a single column, nearly 10 columns having these type of values (max of 5 choices)
thats why i decided this way

INSERT INTO tbl_test(fld_Id,fld_name,fld_interests)
VALUES('1','Andy','A1'',''A3'',''A4')

can i change my query like the above
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24105206
I suggested using double quotes. Did u try it? did it work for you?
0
 

Author Comment

by:nissiinfotechpvtltd
ID: 24105241
hai rockiroads

i tried it, its working, but im asking the diff between that query with below

INSERT INTO tbl_test(fld_Id,fld_name,fld_interests)
VALUES('1','Andy','A1'',''A3'',''A4')
0
 
LVL 23

Expert Comment

by:adathelad
ID: 24105261
If you want each value enclosed in single quotes, then no you need to stick with the original example I gave. In your version, what you will end up with in the column is:
A1','A3','A4

An alternative, depending on what you want to do with the field, is not enclose them in quotes, just stored 'A1,A2,A3' which will simplify it. Though that may not be an option depending on what you do with the field.

IMHO, I think you may encounter a performance hit from storing it like this. For example, if you want to to query all records where A2 is an interest, it can't use an efficient index seek. It will have to do a scan. I've encountered CSV fields like this before and the best case in those has been to split out into a separate normalised table. Though I understand you have a number of these columns.
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
ID: 24105271
single quotes can be used to wrap strings as well as double quots. It doesnt do no harm in using both. But whatever you start with, you must end with.  Now if you have two single quotes it is going to assume you are starting another string so syntax is going to become incorrect.
0
 
LVL 23

Expert Comment

by:adathelad
ID: 24105279
To test what would actually get inserted, to get a feel for what various things do, just run a simple test select:

e.g. your example:
SELECT 'A1'',''A3'',''A4'

correct example:
SELECT '''A1'',''A3'',''A4'''
0
 

Author Comment

by:nissiinfotechpvtltd
ID: 24105341
thanks to all of u guys
i just missed to test that query with my query
i got a result
0
 
LVL 23

Accepted Solution

by:
adathelad earned 300 total points
ID: 24105378
I think it's a bit harsh to close the question without acknowledging the quick, concise and accurate answers given tbh. If you got the answer you expected, then at the very least you got confirmation from us about the way to go. You did also get further suggestions on alternatives.
0
 

Author Closing Comment

by:nissiinfotechpvtltd
ID: 31568418
thanks for guiding step by step
thanks to all of u
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 this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

715 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