By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Insert a row into a table with 2 foreign keys

P: 25
Ok, here's my problem, I'll try to explain it the best I can, it may be something really simple.
I'm working in a .NET web page to add new users into the table in SQL.
I have the main table that will be updated with the new information, this table has 2 foreign keys (ids), each one of them to different tables.
Problem comes when a new user is been added, the web page displays the descriptions for these 2 fields, but in the main table I have the id's.

How can I insert the data in one single query instead of creating first 2 queries to get the id's based on the descriptions saving them into variables, and then finally insert all values to the main table.

I want to insert: 'NameofGuy', 'Administrator', 'CompanyX'
into TableA.
TableA fields: username,role,company
but role and company are ids (int), it should look like this:
'NameofGuy', 1, 2

TableB (roles): id, roleDescription
____________1 , 'Administrator'

TableC (company): id, companyName
_________________1, 'CompanyW'
_________________2, 'CompanyX'

Hope is clear.

Any help is appreciated.
Sep 23 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 2,878
Option 1:

Create an updateable view. During updating of the view, update the underlying tables, instead.

Option 2:

On your app, you can build your query in such a way that you can update the tables with the necessary values.

Good luck!

-- CK
Sep 23 '08 #2

Expert 100+
P: 1,017
Try this:

Expand|Select|Wrap|Line Numbers
  1. insert into tableA values ('NameofGuy', (Select Top 1 id from tableB where roleDescription = 'Administrator'), (Select Top 1 id from tableC where companyName = 'CompanyX'))
I use Top 1 to make sure that only one record will be returned you can use max or min as well.

Good Luck.
Sep 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.