Please help creating this query...

Hello,
I need some help with this query, I have 2 tables...
I need them have a common int field,...
on the first table it is an auto incrementing field called ID, on the second it is called CID, and is suppose to act as a foreign key.
I am using Enterprise manager and MS SQL 2000 database to manage this thing, but to be honest my database skills dont go far from basic inserts, updates, and deletes.

To make this happen I have set up a relationship to my first table like so FK_table2_table1... primary on first table = id and foregn key on second table = CID. The second table could have multiple records with same CID...

What needs to be achieved here is when I do an insert it should update second table and enter CID as the auto gen number from the first table. What do I need to do?

Lito

So what I am understanding that you want to do is this:

You have the following tables structure:

table1
-----------
id (autogenerated PK)
other fields


table2
-----------------
table2id(autogenerated PK)
CID (FK to table1)
other fields

You want to update table2 CID when you do an insert into table1? If you insert into table1, which means you would get a new id, wouldn't you be doing an insert into table2 as well, since table1.id = table2.CID?

Why don't you just get the identity (id) field back and do another insert?

you are understanding me correctly, but I can't do what you suggest! This is a web application, which means there is a posibility of more then a single user at any given second updating the same tables, and if I get the (id) field back of a wrong insert, my app is useless. Thanks.

Lito

How would you get the wrong id field back?
When you do the insert do something like this:

strSQL = "SET NOCOUNT ON; INSERT INTO table1(field1, field2) VALUES(" & value1 & ", " & value2 & "); SELECT @@IDENTITY; SET NOCOUNT OFF;"

set objRS = objConn.Execute(strSQL)
dim intID
intID = objRS(0)

and then your only recordset row is that of the identity field...

so now you can say

strSQL2 = "INSERT INTO table2(CID, otherfield) VALUES ( " & intID & ", " & othervalue & "); "

Thanks it works the way I need it to, I didn't know you could execute multiple statements in same sql string! And what does "SET NOCOUNT ON
/OFF" do?

Thanks alot,
Lito

Oh I have realized something just now...
and I dont need to get the Identety field but another field and use it as a counter Dont ask why

Im trying this

INSERT INTO tbl1 (myID, 'other', 'values',...) VALUES (MAX(myID)+1, 'other', 'values', ...)

and it doesn't want to work!!!!

I can swear I have done this before.

Ok, so do the insert and pull back the identity, and the do a select of the counter where id = the identity field you pulled back in the insert statement.

Hi, thanks again
I did almost exaclty as you suggested...
SELECT TOP 1 myID FROM myTable ORDER BY myID DESC
and then just add to it.

T y,
Lito

That's really not a good way to do it...

What if someone else adds a record a second later? If you are POSITIVE that won't happen, I guess you could use your method. But the only way of getting back the correct record for sure is using the @@IDENTITY.

J

You are absolutly right!
I am doing the exact thing i wanted to avoid. Becase the DB structure is funky here and I am not authorized to make any significant changes this is my solution...

You have helped more then you know, thanks again.

Lito