SELECT inside INSERT query?

Question

I am trying to insert the user id from table users inside table session , field session_user, using textbox , but it seems it doesn't work ..

Here is my SQL code, I am using visual studio and trying to insert to a SQL Server table

SqlCommand addsession = new SqlCommand
  ("insert into dbo.session(session_user) 
    values (select user_id from dbo.users where username = '" + TextBox1.Text + "')", 
   badersql);

And you SHOULD by all means use a parametrized query instead of concatenating together your INSERT statement...... — marc_s
– marc_s, Commented Dec 11, 2010 at 19:22

casablanca · Accepted Answer · 2010-12-11 18:33:55Z

6

You shouldn't use the VALUES keyword when you're doing an INSERT ... SELECT:

insert into dbo.session (session_user) select user_id from dbo.users ...

answered Dec 11, 2010 at 18:33

casablanca

70.9k8 gold badges139 silver badges155 bronze badges

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

1 Comment

Bader Over a year ago

SqlCommand addsession = new SqlCommand(" insert into dbo.session (session_user) select user_id from dbo.users where username = '" + TextBox1.Text + "'", badersql); , i tried to do this but it gave me this error " Incorrect syntax near the keyword 'session_user'."

ClosureCowboy · Accepted Answer · 2010-12-11 20:54:17Z

4

If you are inserting the result of a query into another table, just leave out the VALUES keyword.

The VALUES keyword can always be replaced by a simple SELECT 'dummy', 'value' of the values you want to insert, but I suggest you still use VALUES whenever you want to make it clear that your results do not come from a query.

That being said, please use parameterized queries!! Imagine if someone were to enter the following text into TextBox1:

' or 1 = 1

What would happen?

edited Dec 11, 2010 at 20:54

answered Dec 11, 2010 at 18:39

ClosureCowboy

21.7k14 gold badges59 silver badges72 bronze badges

1 Comment

Bader Over a year ago

thanks for the tip , but this project is will be locally on my computer and it won't be used in the internet now or in the future, i know parametrized queries are the correct thing to do , but no need to use them right now , thanks again

marc_s · Accepted Answer · 2010-12-11 19:21:59Z

2

To insert records from a query use this insert syntax:

insert into dbo.session (session_user) 
select user_id from dbo.users where username = '" + TextBox1.Text + "'

You may want to do a select top 1 userid if you are expecting one row to be inserted like in the values statement.

edited Dec 11, 2010 at 19:21

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 11, 2010 at 18:40

Steve

2,1834 gold badges22 silver badges32 bronze badges

1 Comment

marc_s Over a year ago

If you post code or XML, please highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it!

Bader · Accepted Answer · 2010-12-12 18:22:56Z

0

i did it , the problem was that i can not name my record session_user , so i replaced with se_user and that solve the problem .

thank u all for ur help

so the correct sql statement is

insert into sessions (se_user) select USER_ID from users where username = '';

answered Dec 12, 2010 at 18:22

Bader

2071 gold badge8 silver badges21 bronze badges

Collectives™ on Stack Overflow

SELECT inside INSERT query?

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related