Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: sql variables in an SQL transaction?

    Hi

    I'm trying to write a single sql statment which updates various tables using a transaction process, how ever, i need the value from an auto-incrementing primary field in the first table, and store its value in the second table.

    I'm sure i can do it using a SELECT statement, but there must be a better way - i'm sure i've seen it before where sql can create and store its own variables!

    thanks in advance!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database system?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    sorry.....its Microsoft SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no prob

    moving thread to microsoft sql server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    It sounds like you want to use scope_identity() to get the value of the identity column after the 1st insert in the transaction, then carry that forward to the subsequent inserts within the transaction.

    I much prefer a 1 column, 1 row table that I increment and pull from to get my primary key. That way multiple procs can quickly hit it and not block each other during the key creation.

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by tomh53
    I much prefer a 1 column, 1 row table that I increment and pull from to get my primary key. That way multiple procs can quickly hit it and not block each other during the key creation.
    Huh? Use the IDENTITY column. That is what is was designed to do.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    ok, i think i'm on the right track, have researched into the @@IDENTITY (is this the same as scope_identity() ?). I'm still trying to get my head round the syntax, am i right in saying that if i use an INSERT command for example, then the value of the global @@IDENTITY will be the primary key of the table i've just inserted into, and then if i INSERT into a different table, the @@IDENTITY will then change....or am i errr way off?

    thanks again

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Have you checked BOL for the difference in scope_identity() and @@identity? There is a very good discussion there, and there ** is ** a difference!

    -- This is all just a Figment of my Imagination --

  9. #9
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    sorry mate, bit of a newbie, whats 'BOL'?

  10. #10
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Books OnLine.

    -- This is all just a Figment of my Imagination --

  11. #11
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    hi again

    nearly cracked it now, this is the code i am using:

    INSERT INTO CUSTOMER(name)
    VALUES('joe bloggs')

    SELECT @@IDENTITY AS 'new_customer_id';

    INSERT INTO VENUE(customer_id, business_name, address)
    VALUES('new_customer_id', 'a business name', 'a business address')

    how ever, i keep getting the following error:

    syntax error converting the varchar value 'new_customer_id' to a column of data type int.

    This is really confusing as i have double checked that the customers identity field is an integer and it is! I have also used:

    print 'new_customer_id';

    And this gives me an integer! Does the @@IDENTITY not take the datatype and just makes it into a varchar? If so how can i convert it into an INT?

    thanks

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to learn how to declare and use variables. Books Online!
    Code:
    declare	@new_customer_id int
    
    INSERT INTO CUSTOMER(name)
    VALUES('joe bloggs')
    
    SET @new_customer_id = @@IDENTITY
    
    INSERT INTO VENUE(customer_id, business_name, address)
    VALUES(@new_customer_id, 'a business name', 'a business address')
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    yes sir! Thanks for that....

    ps, Books online, is that a general term or is it a url?

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The quickest way to get to it is through the Query Analyzer menu. Click on Help/Transact SQL Help.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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