Insert data into tables linked with foreign key

Question

First of all I tried this Insert Data Into Tables Linked by Foreign Key and didn't get the answer.

I have 3 tables:

Table: Customers

ID -------LastName-------FirstName-------PhoneNumber

Table: Order

ID-------Status-------CustomerID

Table: OrderLine

ID-------OrderID-------Product-------Quantity-------PricePerUnit

I run the following query

SqlCommand myCommand2 = 
   new SqlCommand(@"INSERT INTO Order (Status, CustomerID) 
                    VALUES(13016, SELECT ID FROM Customers WHERE FirstName = 'Garderp')", 
                  myConnection);`

and it throws exception

Syntax error near Order

How can I add data into table with foreign key in SQL Server 2008 especially in this particular case?

FYI you can only accept one answer :) I think Icarus has the most complete one. — JNK, Jan 13 '12 at 17:58

score 5 · Answer 1 · answered Jan 13 '12 at 17:44

ORDER is a reserved keyword in SQL Server (used in the ORDER BY operation).

You need to delimit that name with brackets:

"INSERT INTO [Order] (Status, CustomerID) VALUES "

That will cause SQL Server to treat it as an object name instead of reading it as a keyword.

score 5 · Accepted Answer · edited Nov 01 '19 at 18:45

5

It should be:

SqlCommand myCommand2 = new SqlCommand(@"INSERT INTO [Order] (Status, CustomerID) " 
   + " SELECT 13016, ID 
       FROM Customers 
       WHERE FirstName = 'Garderp')"
, myConnection);

edited Nov 01 '19 at 18:45

Braiam

1
11
47
78

answered Jan 13 '12 at 17:46

Icarus

63,293
14
100
115

Your SQL statement is missing a ( – thang Feb 24 '16 at 06:41

score 2 · Answer 3 · answered Jan 13 '12 at 18:13

2

Order is keyword in SQL, you have to bracket all keyword if you use it as table name or column.

answered Jan 13 '12 at 18:13

Johnny Circle

41
1
1
4

score 0 · Answer 4 · answered Jan 13 '12 at 17:46

0

Try using this query

INSERT INTO [Order]
( Status, CustomerID )
SELECT 13016, ID
    FROM Customers
    WHERE FirstName = 'Garderp'

answered Jan 13 '12 at 17:46

Kaerber

1,623
16
21

Mithrandir · Answer 5 · 2012-01-13T17:53:16.180

0

SqlCommand myCommand2 = new SqlCommand(@"INSERT INTO Order 
(Status, CustomerID)
VALUES " + "(13016, 
(SELECT ID FROM Customers WHERE FirstName = 'Garderp'))", myConnection);

edited Jan 13 '12 at 17:53

answered Jan 13 '12 at 17:46

Mithrandir

24,869
6
50
66

score -2 · Answer 6 · edited Jan 13 '12 at 17:59

-2

try this

SqlCommand myCommand2 = new SqlCommand(@"INSERT INTO Order (Status, CustomerID) VALUES " + "(13016, SELECT ID FROM Customers WHERE FirstName = \'Garderp\')", myConnection);

if there are more than one customer with name "Gardep", this query would fail. You need to select only one record . Using TOP will be a better idea here

SqlCommand myCommand2 = new SqlCommand(@"INSERT INTO Order (Status, CustomerID) VALUES " + "(13016, SELECT TOP 1 ID FROM Customers WHERE FirstName = \'Garderp\')", myConnection);

edited Jan 13 '12 at 17:59

marc_s

732,580
175
1,330
1,459

answered Jan 13 '12 at 17:46

Just Me

244
1
5

1

-1 - you've provided 20 answers and should know how to mark code at this point. – JNK Jan 13 '12 at 17:48
1

Also it's a bad answer since you don't account for the reserved word use as a table name, and `TOP 1` will discard additional results he may want. – JNK Jan 13 '12 at 18:01
if u dont use top 1 and there are multiple rows, the query would fail. You seem like a newbie. And NO, i dont know how to format the code. I believe if you have skills, you dont need spoon feeding – Just Me Jan 17 '12 at 12:03
Actually it wouldn't fail if you made it `SELECT 13016, id from customers`, which is another reason this is a wrong answer - the approach is inappropriate for the issue. Code tags are not about spoon feeding, they are to make the code more readable. – JNK Jan 17 '12 at 13:12

Insert data into tables linked with foreign key

6 Answers6