Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Posts
    46

    Unanswered: HELP: INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...) - Doesn't work

    I need to be able to increment myID field by one on insert, and this doesn't seem to be doing it

    INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...)

    I could swear i have done it before. Please help.

    Lito

  2. #2
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Re: HELP: INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...) - Doesn't work

    Originally posted by lito
    I need to be able to increment myID field by one on insert, and this doesn't seem to be doing it

    INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...)

    I could swear i have done it before. Please help.

    Lito
    how about the use of a variable...

    declare @maxid int
    select @maxid = max(myid)+1 from ...

    insert (...myID,...) values(...@maxid...)


    might be worth a try

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is there a reason you are not using an identity field?

    If so, then you will need to post your entire insert statement, not just the scrap you have supplied. We need more information about what you are trying to do.

    blindman

  4. #4
    Join Date
    Nov 2003
    Posts
    46
    rocket39:
    thanks for the sugestion, I never tought of that, it works great!.

    blindman:
    there is a reason why I am not using an Identity filed and that reason is because this database is aprox. 10 old was originaly a DBase. Also I am not the database administrator for this client and not authorised to make any significant changes to it. Plus this is a web based app. and is getting to be huge any change to db means hours of recoding. It is very stupid and frustrating at times.

    Thanks for the help.

    Lito

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look into setting a transaction lock around the pair of sql statements to fetch the max and then use it to insert a new number

    without such a lock you are only asking for trouble
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2003
    Posts
    46
    Originally posted by r937
    look into setting a transaction lock around the pair of sql statements to fetch the max and then use it to insert a new number

    without such a lock you are only asking for trouble
    do you think that TABLOCK would be sufficient?...

    SET NOCOUNT ON;
    DECLARE @newID INT;
    SELECT @newID = (MAX(elementID)+1) FROM tblCattle TABLOCK; <<<<<< table LOCKED
    INSERT INTO table1 (...values..., myID, ... values)
    VALUES (...values..., @newID, ...values);
    INSERT INTO table2 (...values..., myID, ... values)
    VALUES (...diff. values..., @newID, ...diff values);
    SET NOCOUNT OFF;

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't think that's enough

    look into BEGIN TRANSACTION


    rudy

  8. #8
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: HELP: INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...) - Doesn't work

    Originally posted by lito
    I need to be able to increment myID field by one on insert, and this doesn't seem to be doing it

    INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...)

    I could swear i have done it before. Please help.

    Lito
    What about this?

    INSERT (... myID, ...) select ...,(select MAX(myID)+1 from ... ), ...

  9. #9
    Join Date
    Nov 2003
    Posts
    46

    Re: HELP: INSERT (... myID, ...) VALUES(... MAX(myID)+1, ...) - Doesn't work

    Originally posted by snail
    What about this?

    INSERT (... myID, ...) select ...,(select MAX(myID)+1 from ... ), ...
    It gives me an error, That I can't have a Select statement in that context... who knows why.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •