INSERT INTO w/ Multiple Tables 1

[bobbyr]

I currently have 2 tables, TeamMember and Stats. The TeamMember table has a MemberId (AutoNumber), Name, and Number. The Stats table has the MemberId, Hits, HomeRuns, and Strikeouts fields. When I do an Insert Into for the TeamMember table, the MemberId is auto generated. How do I then Insert that MemberId into the Stats table to associate stats with the team member at the same time?

 

[Klotzki]

I think you might have to do the 2nd insertion manually. After a record is inserted in TeamMember table, do a Last function SQL query to get the last record in the table.

Something like this,

<CFQUERY NAME=&quot;LastTeamMemberRecord&quot;>
SELECT Last(MemberID) AS LastMemberID
FROM TeamMember;
</CFQUERY>

Use the LastMemberID value in this query in your second query that will do the insertion on the Stats table.

HTH,

Klotzki

 

[bobbyr]

Thank you very much. It worked perfect. The problem I was having was with the Last(MemberID) part.

 

[olmos]

I have the same question. The last id is not getting put into the table that I want. I also have two tables with autonumber , but I'm using Oracle. The tables are one for customer info and the other for computer info, I need to get the custid (the foreign key) put into computer info table. I used the example above , changed it with my db info and it is not working. Can you explain where to put the query. I put it before the insert query on the action page , but it didn't work.

thanks,
olmos

 

[iza]

make sure it is not a sql problem (i guess it is). If it is, you're on the wrong forum !!!!