Solved

SQL Server Insert Query - single quote problem

Posted on 2009-04-09
13
899 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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…
In this article I will describe the Backup & Restore 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.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

785 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