#1
  1. MACTEP /\OMACTEP
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    Seattle, WA
    Posts
    150
    Rep Power
    13

    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
    Sometimes just a few hours of trial and error debugging can save minutes of reading the manual.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    11
    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?
  4. #3
  5. MACTEP /\OMACTEP
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    Seattle, WA
    Posts
    150
    Rep Power
    13
    Originally posted by jstrohofer
    Why don't you just get the identity (id) field back and do another insert? [/B]
    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
    Sometimes just a few hours of trial and error debugging can save minutes of reading the manual.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    11
    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 & "); "
  8. #5
  9. MACTEP /\OMACTEP
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    Seattle, WA
    Posts
    150
    Rep Power
    13
    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
    Sometimes just a few hours of trial and error debugging can save minutes of reading the manual.
  10. #6
  11. MACTEP /\OMACTEP
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    Seattle, WA
    Posts
    150
    Rep Power
    13
    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.
    Sometimes just a few hours of trial and error debugging can save minutes of reading the manual.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    11
    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.
  14. #8
  15. MACTEP /\OMACTEP
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    Seattle, WA
    Posts
    150
    Rep Power
    13
    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
    Sometimes just a few hours of trial and error debugging can save minutes of reading the manual.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    11
    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
  18. #10
  19. MACTEP /\OMACTEP
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Location
    Seattle, WA
    Posts
    150
    Rep Power
    13
    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...
    PHP Code:
    SET NOCOUNT ON;
    DECLARE @
    newID INT;
    SELECT @newID = (MAX(elementID)+1FROM tblCattle
    INSERT INTO table1 (...values..., myID, ... values
    VALUES (...values..., @newID, ...values);
    INSERT INTO table2 (...values..., myID, ... values)
    VALUES (...diffvalues..., @newID, ...diff values);
    SET NOCOUNT OFF
    You have helped more then you know, thanks again.

    Lito
    Sometimes just a few hours of trial and error debugging can save minutes of reading the manual.

IMN logo majestic logo threadwatch logo seochat tools logo