Solved

SQL Server Insert Query - single quote problem

Posted on 2009-04-09
13
900 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

789 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