INSERT doable in SQL?

[jrock2468]

Is this doable with one SQL query? There are two tables:

tblPerson
----------------
PersonId
PersonFirstName
PersonLastName
-> refPersonGroupId
|
|
| (linked by)
|
|
| tblPersonGroup
| ----------------
-> PersonGroupId
PersonGroupName


So tblPerson.refPersonGroupId references tblPersonGroup.PersonGroupId.

I would like to insert a new entry into tblPerson without doing a separate query to find out what refPersonGroupId should be. If I know the new entry's PersonFirstName, PersonLastName, and the PersonGroupName to which they belong, how can I insert the entry? Eventually I'd like to make this a stored procedure where the names and the PersonGroupName are the inputs.

Thanks!

 

[PHV]

INSERT INTO tblPerson (PersonFirstName, PersonLastName,
refPersonGroupId)
SELECT 'first name here', 'last name here', PersonGroupId
FROM tblPersonGroup WHERE PersonGroupName = 'group name here'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrock2468]

You rock it like a hurricane =)

 

[jrock2468]

PHV -

One more quick addition to the last addition. Your query works great. Now, if I need to add on additional groups, like refPersonGroupId1, refPersonGroupId2, refPersonGroupId3... how would I extend your query?

I tried this, but it didn't work:

INSERT INTO tblPerson (PersonFirstName, PersonLastName,
refPersonGroupId1, refPersonGroupId2)
SELECT
'first name here',
'last name here',
PersonGroupId
FROM tblPersonGroup WHERE PersonGroupName = 'group name 1 here',
PersonGroupId
FROM tblPersonGroup WHERE PersonGroupName2 = 'group name 2 here'

Thanks again for the first speedy response!

 

[PHV]

like refPersonGroupId1, refPersonGroupId2, refPersonGroupId3
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Using PHV's query, make the WHERE clause

WHERE PersonGroupName IN ( 'Group Name1', 'Group Name2', 'Group Name3')

for as many groups as you want.

 

[jrock2468]

PHV and Gomom -

I read PHV's link... are you saying that this won't work? I'm trying to simplify what I'm really doing into something that's easy to post and comment on. In reality there are two fields that are references to the primary key of another table that need to be updated (hopefully in one query).

I'm not sure how Golom's addition works. Here's what a simplified version of my two tables:

tblPerson
----------------
PersonId
PersonFirstName
PersonLastName
|-> refPersonGroupId1
|-> refPersonGroupId2
|
| (linked by)
|
|
| tblPersonGroup
| ----------------
-> PersonGroupId
PersonGroupName



I tried changing my query with Golom's suggestion, but it complains about syntax errors in the INTO statement:

INSERT INTO tblPerson (PersonFirstName, PersonLastName, refPersonGroupId1, refPersonGroupId2)
SELECT 'first name', 'last name', PersonGroupId
FROM tblPersonGroup WHERE PersonGroupName IN ( 'Group Name1', 'Group Name2')

Thanks guys.

 

[jrock2468]

Actually error is 'number of query values and destination fields are not the same'

 

[PHV]

I read PHV's link... are you saying that this won't work?
The repeating refPersonGroupId violate First Normal Form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrock2468]

-- The repeating refPersonGroupId violate First Normal Form

Ok, I oversimplified what I'm actually dealing with because my ability to draw clear ascii art is limited to two tables.

In reality, refPersonGroupId1 and refPersonGroupId2 don't point to the same table. They point to two different tables, one for buyers and one for sellers. Neither of those two tables (call them tblBuyers and tblSellers) share any of the same information.

Here's an attempt to draw it (still simplified a bit):

tblTrade
----------------
TradeId
TradeName
|---->refBuyerId
| |->refSellerId
| |
| |
| |
| |
| | tblBuyers
| | ----------------
---|->BuyerId
| BuyerName
|
|
|
| tblSellers
| ----------------
|->SellerId
SellerName


How would you go about inserting into tblTrade if you know the BuyerName and SellerName? Thanks

 

[PHV]

INSERT INTO tblTrade (TradeName, refBuyerId,
refSellerId)
SELECT 'trade name here', BuyerId, SellerId
FROM tblBuyers, tblSellers
WHERE BuyerName = 'buyer name here'
AND SellerName = 'seller name here'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886