sql insert values into specific column

Question

I'm trying to insert a nvarchar value into a specific column in a table. The value can possibly have reserved words and single quote chars(as in don't).

what I have is:

set @myString= REPLACE(@myString, '''', '''''');
set @ExecStatement = 'INSERT INTO #TempTable('+@ColumnName+') VALUES('''+@myString+''')';
exec (@ExecStatement)

This works for the vast majority of items but I get this error message:

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'We didn't understand, please resend, or type HE'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'We didn't understand, please resend, or type HE'.

and I'm actually missing the remainder of the text that's supposed to be there. It's supposed to be "We didn't understand, please resend, or type HELP and someone will contact you".

Any help would be greatly appreciated.

Since you are properly escaping the string, it should work. Is there a trigger on the table that is using dynamic SQL? — Andomar
– Andomar, Commented Apr 18, 2014 at 17:44
Can you do a SELECT @ExecStatement before the exec (@ExecStatement) and show us what the result is? — Tom
– Tom, Commented Apr 18, 2014 at 17:49
Perhaps the problem is the length of the strings. Can you show the declare statement. — Gordon Linoff
– Gordon Linoff, Commented Apr 18, 2014 at 18:02
@Tom here's the select results: INSERT INTO #TempTable(Col1) VALUES('We didn''t understand, please resend, or type HELP and someone will contact you.') — shadonar
– shadonar, Commented Apr 18, 2014 at 18:09

Tom · Accepted Answer · 2014-04-18 18:22:43Z

1

Based on the comment that your output string is:

INSERT INTO #TempTable(Col1) VALUES('We didn''t understand, please resend, or type HELP and someone will contact you.')

You will need to escape the quotes surrounding the string, and quadruple quote the ones that should actually be quotes... Something like

INSERT INTO #TempTable(Col1) VALUES(''We didn''''t understand, please resend, or type HELP and someone will contact you.'')

answered Apr 18, 2014 at 18:22

Tom

7,7401 gold badge26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

shadonar Over a year ago

meaning set @myString= REPLACE(@myString, '''', ''''''); would actually be set @myString= REPLACE(@myString, '''', '''''''''');?

Tom Over a year ago

Keep the REPLACE on @myString the same, but then do the same thing on @ExecStatement

shadonar Over a year ago

So doing the REPLACE ON THE @ExecStatement did some funny things, so I just did set @myString= REPLACE(@myString, '''', ''''''''''); and that resolved the issue.

Anthony Horne · Accepted Answer · 2014-04-18 17:44:59Z

0

Using square brackets may help:

set @myString= REPLACE(@myString, '''', '''''');
set @ExecStatement = 'INSERT INTO #TempTable(['+@ColumnName+']) VALUES('''+@myString+''')';
exec (@ExecStatement)

answered Apr 18, 2014 at 17:44

Anthony Horne

2,5202 gold badges31 silver badges54 bronze badges

3 Comments

shadonar Over a year ago

the column name isn't the problem I'm having, it's the @myString that's giving me issues.

Gordon Linoff Over a year ago

@shadonar . . . Somehow, you may know that. However, we don't. It would be helpful if you edited the question to include the value of @ExecStatement after the variable substitution. Or at least the value of @ColumnName.

Anthony Horne Over a year ago

Maybe try VALUES('N''

Collectives™ on Stack Overflow

sql insert values into specific column

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related