Insert multiple unknown foreign key values

Databases
1

Guys, I posted this on a couple of sites and I’m surprised I haven’t received a response because honestly I thought it was a common situation. Then it hit me to come to Sitepoint, so maybe you can help me out? I’m using MSSQL. Thanks.

I’m trying to insert some data into 1(notes) table that has several foreign keys. The problem is that I’m not given the id of the foreign key, but another field from each table. How do I find the PK of another table from another field in those tables and INSERT the pk into the notes table? For example:

cars

(pk) car_id (ai)
car_name

images

(pk) image_id (ai)
image_name

notes

(pk) note_id (ai)
(fk) image_id
(fk) car_id
note_text
date_created

I supply the values :
noteid(not really supplied because auto incrmenting),mustang.jpg,Mustang,Stuff about this car,5/3/12

I have tried:

INSERT INTO notes (image_id,car_id, note_text,date_created)
VALUES ( (SELECT img_id FROM images WHERE image_name = ‘mustang.jpg’), (SELECT car_id FROM cars WHERE car_name = ‘Mustang’), ‘Stuff about this car’, ‘5/3/12’))

I always get the “Subqueries are not allowed in this context. Only scalar expressions are allowed.”

2 
INSERT 
  INTO notes 
     ( image_id
     , car_id
     , note_text
     , date_created )
SELECT img_id 
     , ( SELECT car_id 
           FROM cars 
          WHERE car_name = 'Mustang' )
     , 'Stuff about this car'
     , '5/3/12'
  FROM images 
 WHERE image_name = 'mustang.jpg'

will fail if there is more than one car called ‘Mustang’

3

Real quick, I appreciate the quick response and I’m going to try this in a little bit. Question though looking at this real quick:

SELECT img_id 
     , ( SELECT car_id 
           FROM cars 
          WHERE car_name = 'Mustang' )
     , 'Stuff about this car'
     , '5/3/12'
  FROM images 
 WHERE image_name = 'mustang.jpg'

Forget the car subquery in the above statement, you have SELECT img_id,what is returned from the subquery for car, ‘Stuff about this car’, ‘5/3/12’. I don’t understand how this won’t error because this query should be expecting:

SELECT image_id, car_id, note_text, date_created but you have the literals [‘Stuff about this car’, ‘5/3/12’] . Would this SELECT statement error because the fields ‘Stuff about this car’, ‘5/3/12’ don’t exist? Thanks again for the help.

4

I dont see the need for the first two tables. Since car_name and image_name will be specific to each record in the notes table !! So just add those details directly into the notes table.

5

best way to find out is to test it

run the SELECT by itself and see what it produces, then stick it into the INSERT statement as shown once you’re convinced

:slight_smile:

6

Thanks for the replies guys. I have been swamped at work and had not had a chance, until now, to really look at your responses.

I don’t see the need for the first two tables.

I haven’t actually designed the database, but wanted to test out a few things before I did. Forget this example for a second, are you saying that you’ll usually not have to INSERT multiple primary keys from other tables into a table? I see myself needing this a lot in the finished design.

best way to find out is to test it

Sorry man. As I said I was at work and really couldn’t test it. I’ve got some time and tested it. I ran both sub queries individually and they both worked fine. When I run the full query I receive the following error in SSMS 2005 Express.

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '`'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ','.

So SQL Server is not liking the combination of the 2 queries. They both run fine independently but when run together I receive the error.

7

you’ve typed something incorectly see what i did there?

that’s a mysql backtick, and has no place in a ms sql server query

look again at your sql

:slight_smile:

8

haha, I can’t believe it. I swear man I’m not a complete noob. I replied with utter confidence that I had checked and re-checked that everything was entered right. Sure enough there was a little gray tick that looked like a piece of dirt on my screen :slight_smile: I mean it was tiny :slight_smile: Thanks brother.