Should I use a subselect?

[work4free]

I have an online form people fill out and their info is put into our database. I have two tables - tblPerson and tblInfo. The primary key from tblPerson is a foreign key in tblInfo. The way it is setup now is:

Query1 = Insert into tblPerson (fname, lname, phone) values ('fname', 'lname', 'phone')

Query2 = Select ID from tblPerson WHERE fname = 'fname' AND lname = 'lname' AND phone = 'phone'

Query3 = Insert into tblInfo (building, room, incident, ID) values ('building', 'room', 'incident', 'Query2')

would it be better/easier to have just two queries, where the second and third queries are combined, utilizing a subselect?

note: I'm using SQL Server 2000.
on the webpage i am using PHP to store and retrieve variables, thus the Query1, 2 & 3. I'm just trying to figure out the SQL to use to plug into PHP.

 

[nigelrivett]

I take it ID is an identity?

Query1 = Insert into tblPerson (fname, lname, phone) values ('fname', 'lname', 'phone')

Query2 = Select @ID = scope_identity()

Query3 = Insert into tblInfo (building, room, incident, @ID) values ('building', 'room', 'incident', 'Query2')

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[work4free]

yes ID is an identity.

thus, should i use "SELECT scope_identity()" or should i use "SELECT @@IDENTITY"? i have never used either one of these but after looking at my Books Online documentation, it looks as though i should use the latter. and the value will be stored in the variable Query2. am i on the right track?

 

[SQLSister]

NO never use @@identity, it is unreliable. NIgel was correct - use Scope_identity. The reason why you do not use @@IDentity is that it will return the identity on another table if there is a trigger on the table which inserts into an differnt table with an identity field. While you may not have a tirgger now, you can;t predict if you will ever have one and this causes a serious data integrity problem when it happens. One you may not even learn about immediately.

Suggest you put all this on a stored procedure and use transactions so that you can rollback the whole transaction if there is an error. Otherwise you are risking a data integrity problem.

Questions about posting. See faq183-874

 

[work4free]

i tried using the scope_identity() but it is not returning the correct value. the identity is set so that it starts at 1 and adds 1 - essentially being an autonumber that is found in Access. I currently have 100 items in the database. when i execute the next INSERT and select the scope_identity(), i would assume the value to be 101, however it is always 2.

 

[mrdenny]

Did you manually put in the 100 items in the database, or were they numbered by the identity? If you put them in by hand, you need to set the identity to start at 101, otherwise it will attempt to reuse the numbers.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[work4free]

they were put in using the identity. they started at 1 and counted up to 100 using the identity increment 1.

 

[ESquared]

What value is scope_identity returning?

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[work4free]

it keeps returning 2. like i said....it is setup to start at 1 and add 1. thus, i assume it keeps getting 1 and adding 1 to it, not the next number in the list...which is around 110 now.

?????

 

[ESquared]

That doesn't sound right. Show your code that's failing.

-------------------------------------
A sacrifice is harder when no one knows you've made it.