SQL Script Failure

Question

Please help: I am experiencing failure running the below script at the section like 'Apple_For_Lunch - breakfast earlier'.

INSERT INTO TableB
                  (LoopValue, Columnvalue)
VALUES     ('Apple_For_Lunch - breakfast earlier', N'Select Columnvalue from TableA where Columnvalue like 'Apple_For_Lunch - breakfast earlier'')

Answer 1

You need to escape single quotes in literals with 2 quotes. Try:

INSERT INTO TableB (LoopValue, Columnvalue)
VALUES     ('Apple_For_Lunch - breakfast earlier', N'Select Columnvalue from TableA where Columnvalue like ''Apple_For_Lunch - breakfast earlier''');

Answer 2

Maybe you need something like this:

INSERT INTO TableB (LoopValue, Columnvalue)
Select 'Apple_For_Lunch - breakfast earlier', Columnvalue
from TableA
where Columnvalue = 'Apple_For_Lunch - breakfast earlier'

Probably some column names need adjustments. If you want to find substrings, then try where Columnvalue LIKE '%Apple_For_Lunch - breakfast earlier%'.

Answer 3

Hi @Samuel Thomas ，

By default, ' is the boundary character of the string, if you include ' in the string, you must use two ', and the first ' is the escape character.

I have provided an example, please refer to:

    select 'Apple_For_Lunch - breakfast earlier' col1,  
    N'Select Columnvalue from TableA where Columnvalue like ''Apple_For_Lunchbreakfast earlier''' col2  
    into #test  
      
    select * from #test  

When the escape character is missing, the statement returns an error：

    select 'Apple_For_Lunch - breakfast earlier' col1,  
    N'Select Columnvalue from TableA where Columnvalue like 'Apple_For_Lunchbreakfast earlier'' col2  
    into #test  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.