SQL INSERT Query with String variable in it

Question

I have an SQL query in which I need to insert a string variable but I am getting errors from it.

Below is my SQL INSERT query:

Insert into User(Employee, Name, Address, Role, Action) 
Select distinct ur1.Employee, ur1.Name, ur1.Address, ur1.Role, ['%" + action + "%'] as [Action] FROM UserRoles ur1)";

string action="insert delete query in database"

I would like to insert string action into my INSERT statement, but I am getting lots of syntax errors.

Can anyone help me with the query?

Thank you in advance for any help.

Show us the errors please. And maybe you also need to use FROM UserRoles ur1) if you don't want engine complains because ur1.Employee, ... don't exist. — Marco
– Marco, Commented Dec 1, 2011 at 11:47
Do you want the sql for this, if so which DBMS (Sql Server, ACcess Oracle etc), or do you want cosde, if so what language. — Tony Hopkinson
– Tony Hopkinson, Commented Dec 1, 2011 at 11:48

tobiasbayer · Accepted Answer · 2011-12-01 11:47:54Z

2

The SQL operator for string concatenation is ||.

answered Dec 1, 2011 at 11:47

tobiasbayer

10.4k4 gold badges48 silver badges66 bronze badges

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

2 Comments

gymcode Over a year ago

hi CodeBrikie, what do you mean by string concatenation? What do I have to replace with |?

tobiasbayer Over a year ago

@RUiHAO "adding strings". You should replace "+" by "||".

Zohaib · Accepted Answer · 2011-12-01 11:54:48Z

1

Insert into User(Employee, Name, Address, Role, Action) 
Select distinct ur1.Employee, ur1.Name, ur1.Address, ur1.Role,'insert delete query in database' 
FROM UserRoles ur1;

edited Dec 1, 2011 at 11:54

answered Dec 1, 2011 at 11:48

Zohaib

7,1263 gold badges29 silver badges35 bronze badges

2 Comments

FosterZ Over a year ago

ur1.Employee,... doesn't exist check out the comment of @Marco

Marco Over a year ago

As I've already told in my post comment, ur1 is not defined ;)

Marco · Accepted Answer · 2011-12-01 11:52:44Z

1

You should try

INSERT INTO User (Employee, Name, Address, Role, Action) 
SELECT DISTINCT ur1.Employee, ur1.Name, 
                ur1.Address, ur1.Role, 
                ['%insert delete query in database%'] 
FROM UserRoles ur1

answered Dec 1, 2011 at 11:52

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Comments

Andriy M · Accepted Answer · 2011-12-01 13:33:12Z

The part where you are concatenating your action variable to the rest of the script looks something like this after the concatenation:

…
  ['the string value goes here'] as [Action]
…

I'm now going to take a wild guess but it does seem as if you are on SQL Server or on Sybase, because of the square brackets. Now, in either of those two products, the sequences of characters enclosed in square brackets would be considered delimited identifiers. Using the brackets in your example does make sense for [Action] but seems absolutely pointless for the ['...'] part of your script. It is more likely that you meant simply '...', i.e. without the square brackets, which simply stands for a string constant.

One other thing to note: there's an unmatched closing parenthesis at the end of your SQL statement.

Collectives™ on Stack Overflow

SQL INSERT Query with String variable in it

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related