Solved

SQL Server Insert Query - single quote problem

Posted on 2009-04-09
13
896 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 50 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 75 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

17 Experts available now in Live!

Get 1:1 Help Now