Solved

SQL Server Insert Query - single quote problem

Posted on 2009-04-09
13
901 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

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…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

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