An SQL question

Discussion in 'ASP .Net' started by Shelly, Sep 21, 2007.

  1. Shelly

    Shelly Guest

    This is more of an SQL question, but I would like to know how to do it in
    SQL Server.

    In a given table I have two columns of importance. The first is account
    number (and it is a foreign key). The other is agent_id. The combination
    must be unique. What I would like to do is to autoincrement the agent_id
    for a given account number. Example:

    Account Number Agent ID
    1000 1
    1000 2
    1100 1
    1200 1
    1200 2
    1200 3

    When I add a new agent to account number 1200, I would like it to come up
    automatically with 2 for the agent_id.. I know I could do a select on
    account number and return MAX of agent_id. I could then increment that
    value and use that pair for new agent creation. However, I wonder if thee
    is a way to do that automatically in SQL?

    Shelly
    Shelly, Sep 21, 2007
    #1
    1. Advertising

  2. Shelly

    Satish Itty Guest

    Make the Agent ID in the table as IDENTITY column and it will
    autoincrement itself.


    CREATE TABLE [dbo].[YourTable](
    [AGENT_ID] [int] IDENTITY(1,1) NOT NULL,
    [Account Number] [varchar](25) NOT NULL,
    ....
    )

    Shelly wrote:
    > This is more of an SQL question, but I would like to know how to do it in
    > SQL Server.
    >
    > In a given table I have two columns of importance. The first is account
    > number (and it is a foreign key). The other is agent_id. The combination
    > must be unique. What I would like to do is to autoincrement the agent_id
    > for a given account number. Example:
    >
    > Account Number Agent ID
    > 1000 1
    > 1000 2
    > 1100 1
    > 1200 1
    > 1200 2
    > 1200 3
    >
    > When I add a new agent to account number 1200, I would like it to come up
    > automatically with 2 for the agent_id.. I know I could do a select on
    > account number and return MAX of agent_id. I could then increment that
    > value and use that pair for new agent creation. However, I wonder if thee
    > is a way to do that automatically in SQL?
    >
    > Shelly
    >
    >
    Satish Itty, Sep 21, 2007
    #2
    1. Advertising

  3. Shelly

    Just Me Guest

    Well, to get the lastAgentID number you could

    Select max(AgentID) From Agent





    "Shelly" <> wrote in message
    news:...
    > This is more of an SQL question, but I would like to know how to do it in
    > SQL Server.
    >
    > In a given table I have two columns of importance. The first is account
    > number (and it is a foreign key). The other is agent_id. The combination
    > must be unique. What I would like to do is to autoincrement the agent_id
    > for a given account number. Example:
    >
    > Account Number Agent ID
    > 1000 1
    > 1000 2
    > 1100 1
    > 1200 1
    > 1200 2
    > 1200 3
    >
    > When I add a new agent to account number 1200, I would like it to come up
    > automatically with 2 for the agent_id.. I know I could do a select on
    > account number and return MAX of agent_id. I could then increment that
    > value and use that pair for new agent creation. However, I wonder if thee
    > is a way to do that automatically in SQL?
    >
    > Shelly
    >
    Just Me, Sep 21, 2007
    #3
  4. Shelly

    Just Me Guest

    Erm, perhaps Im missing the point there but If you do as you suggest you
    would also need a relation in the agent table. This kinda seems ass about
    face to me. Just because you add an account, why do you need a new agent
    automatically as this implies a one to one relationship which is different
    to that which is in the table shown below.

    In the table shown below the OP seems to have suggested that there is an
    incremental increase in the AgentID for each group of numbers, IE
    1000,1100,1200 ranges each have their own incremental range , but the text
    below is suggesting that the OP could take a MAX( AgentID) to get the
    number.

    This all seems wrong to me !


    "Satish Itty" <> wrote in message
    news:u3lQ6wF$...
    > Make the Agent ID in the table as IDENTITY column and it will
    > autoincrement itself.
    >
    >
    > CREATE TABLE [dbo].[YourTable](
    > [AGENT_ID] [int] IDENTITY(1,1) NOT NULL,
    > [Account Number] [varchar](25) NOT NULL,
    > ....
    > )
    >
    > Shelly wrote:
    >> This is more of an SQL question, but I would like to know how to do it in
    >> SQL Server.
    >>
    >> In a given table I have two columns of importance. The first is account
    >> number (and it is a foreign key). The other is agent_id. The
    >> combination must be unique. What I would like to do is to autoincrement
    >> the agent_id for a given account number. Example:
    >>
    >> Account Number Agent ID
    >> 1000 1
    >> 1000 2
    >> 1100 1
    >> 1200 1
    >> 1200 2
    >> 1200 3
    >>
    >> When I add a new agent to account number 1200, I would like it to come up
    >> automatically with 2 for the agent_id.. I know I could do a select on
    >> account number and return MAX of agent_id. I could then increment that
    >> value and use that pair for new agent creation. However, I wonder if
    >> thee is a way to do that automatically in SQL?
    >>
    >> Shelly
    Just Me, Sep 21, 2007
    #4
  5. Shelly

    Shelly Guest

    "Satish Itty" <> wrote in message
    news:u3lQ6wF$...
    > Make the Agent ID in the table as IDENTITY column and it will
    > autoincrement itself.


    No, I only want it to autoincrement as a subset of account numbers. It does
    not have to be unique. Only the combination of the two must be unique.

    >
    >
    > CREATE TABLE [dbo].[YourTable](
    > [AGENT_ID] [int] IDENTITY(1,1) NOT NULL,
    > [Account Number] [varchar](25) NOT NULL,
    > ....
    > )
    >
    > Shelly wrote:
    >> This is more of an SQL question, but I would like to know how to do it in
    >> SQL Server.
    >>
    >> In a given table I have two columns of importance. The first is account
    >> number (and it is a foreign key). The other is agent_id. The
    >> combination must be unique. What I would like to do is to autoincrement
    >> the agent_id for a given account number. Example:
    >>
    >> Account Number Agent ID
    >> 1000 1
    >> 1000 2
    >> 1100 1
    >> 1200 1
    >> 1200 2
    >> 1200 3
    >>
    >> When I add a new agent to account number 1200, I would like it to come up
    >> automatically with 2 for the agent_id.. I know I could do a select on
    >> account number and return MAX of agent_id. I could then increment that
    >> value and use that pair for new agent creation. However, I wonder if
    >> thee is a way to do that automatically in SQL?
    >>
    >> Shelly
    Shelly, Sep 21, 2007
    #5
  6. Shelly

    Shelly Guest

    "Just Me" <news.microsoft.com> wrote in message
    news:u8iF53F$...
    > Erm, perhaps Im missing the point there but If you do as you suggest you
    > would also need a relation in the agent table. This kinda seems ass about
    > face to me. Just because you add an account, why do you need a new agent
    > automatically as this implies a one to one relationship which is different
    > to that which is in the table shown below.


    It is not when I add an account. It is when I add an agent in the agent
    table to an already existing account in the accounts table. The account to
    agent is one to many, but each of the agent IDs for that account must be
    unique. In the agent table, the account number is a foreign key. It is a
    primary key in the account table.

    >
    > In the table shown below the OP seems to have suggested that there is an
    > incremental increase in the AgentID for each group of numbers, IE
    > 1000,1100,1200 ranges each have their own incremental range , but the text
    > below is suggesting that the OP could take a MAX( AgentID) to get the
    > number.
    >
    > This all seems wrong to me !


    I can take a MAX(AgentID) when I select on accountNumber in the Agent table.
    IOW, where I have a WHERE clause for accountNumber=the_account_number.

    >
    >
    > "Satish Itty" <> wrote in message
    > news:u3lQ6wF$...
    >> Make the Agent ID in the table as IDENTITY column and it will
    >> autoincrement itself.
    >>
    >>
    >> CREATE TABLE [dbo].[YourTable](
    >> [AGENT_ID] [int] IDENTITY(1,1) NOT NULL,
    >> [Account Number] [varchar](25) NOT NULL,
    >> ....
    >> )
    >>
    >> Shelly wrote:
    >>> This is more of an SQL question, but I would like to know how to do it
    >>> in SQL Server.
    >>>
    >>> In a given table I have two columns of importance. The first is account
    >>> number (and it is a foreign key). The other is agent_id. The
    >>> combination must be unique. What I would like to do is to autoincrement
    >>> the agent_id for a given account number. Example:
    >>>
    >>> Account Number Agent ID
    >>> 1000 1
    >>> 1000 2
    >>> 1100 1
    >>> 1200 1
    >>> 1200 2
    >>> 1200 3
    >>>
    >>> When I add a new agent to account number 1200, I would like it to come
    >>> up automatically with 2 for the agent_id.. I know I could do a select
    >>> on account number and return MAX of agent_id. I could then increment
    >>> that value and use that pair for new agent creation. However, I wonder
    >>> if thee is a way to do that automatically in SQL?
    >>>
    >>> Shelly

    >
    >
    Shelly, Sep 21, 2007
    #6
  7. Shelly

    Shelly Guest

    "Just Me" <news.microsoft.com> wrote in message
    news:Ogr6xxF$...
    > Well, to get the lastAgentID number you could
    >
    > Select max(AgentID) From Agent


    Like I said, I know I can do this (adding, of course, the clause WHERE
    accountNumber=theaccountNumber) , but my question is can SQL do this
    automatically on the insert statement into the Agent table? If it can, then
    I can do an ExecuteScalar and return that value from the insert query
    without having to do two queries in succession with the possiblity of a race
    condition where someone else is adding an agent to the same account at the
    same time.

    Shelly

    >
    >
    >
    >
    >
    > "Shelly" <> wrote in message
    > news:...
    >> This is more of an SQL question, but I would like to know how to do it in
    >> SQL Server.
    >>
    >> In a given table I have two columns of importance. The first is account
    >> number (and it is a foreign key). The other is agent_id. The
    >> combination must be unique. What I would like to do is to autoincrement
    >> the agent_id for a given account number. Example:
    >>
    >> Account Number Agent ID
    >> 1000 1
    >> 1000 2
    >> 1100 1
    >> 1200 1
    >> 1200 2
    >> 1200 3
    >>
    >> When I add a new agent to account number 1200, I would like it to come up
    >> automatically with 2 for the agent_id.. I know I could do a select on
    >> account number and return MAX of agent_id. I could then increment that
    >> value and use that pair for new agent creation. However, I wonder if
    >> thee is a way to do that automatically in SQL?
    >>
    >> Shelly
    >>

    >
    >
    Shelly, Sep 21, 2007
    #7
  8. Shelly

    Just Me Guest

    OK, now I understand what you are trying to acheive. I think the answer is
    no there is not.




    "Shelly" <> wrote in message
    news:...
    >
    > "Just Me" <news.microsoft.com> wrote in message
    > news:Ogr6xxF$...
    >> Well, to get the lastAgentID number you could
    >>
    >> Select max(AgentID) From Agent

    >
    > Like I said, I know I can do this (adding, of course, the clause WHERE
    > accountNumber=theaccountNumber) , but my question is can SQL do this
    > automatically on the insert statement into the Agent table? If it can,
    > then I can do an ExecuteScalar and return that value from the insert query
    > without having to do two queries in succession with the possiblity of a
    > race condition where someone else is adding an agent to the same account
    > at the same time.
    >
    > Shelly
    >
    >>
    >>
    >>
    >>
    >>
    >> "Shelly" <> wrote in message
    >> news:...
    >>> This is more of an SQL question, but I would like to know how to do it
    >>> in SQL Server.
    >>>
    >>> In a given table I have two columns of importance. The first is account
    >>> number (and it is a foreign key). The other is agent_id. The
    >>> combination must be unique. What I would like to do is to autoincrement
    >>> the agent_id for a given account number. Example:
    >>>
    >>> Account Number Agent ID
    >>> 1000 1
    >>> 1000 2
    >>> 1100 1
    >>> 1200 1
    >>> 1200 2
    >>> 1200 3
    >>>
    >>> When I add a new agent to account number 1200, I would like it to come
    >>> up automatically with 2 for the agent_id.. I know I could do a select
    >>> on account number and return MAX of agent_id. I could then increment
    >>> that value and use that pair for new agent creation. However, I wonder
    >>> if thee is a way to do that automatically in SQL?
    >>>
    >>> Shelly
    >>>

    >>
    >>

    >
    >
    Just Me, Sep 22, 2007
    #8
  9. "Shelly" <> wrote in message
    news:...
    >
    > "Just Me" <news.microsoft.com> wrote in message
    > news:Ogr6xxF$...
    >> Well, to get the lastAgentID number you could
    >>
    >> Select max(AgentID) From Agent

    >
    > Like I said, I know I can do this (adding, of course, the clause WHERE
    > accountNumber=theaccountNumber) , but my question is can SQL do this
    > automatically on the insert statement into the Agent table? If it can,
    > then I can do an ExecuteScalar and return that value from the insert query
    > without having to do two queries in succession with the possiblity of a
    > race condition where someone else is adding an agent to the same account
    > at the same time.
    >
    > Shelly
    >
    >>
    >>
    >>
    >>
    >>
    >> "Shelly" <> wrote in message
    >> news:...
    >>> This is more of an SQL question, but I would like to know how to do it
    >>> in SQL Server.
    >>>
    >>> In a given table I have two columns of importance. The first is account
    >>> number (and it is a foreign key). The other is agent_id. The
    >>> combination must be unique. What I would like to do is to autoincrement
    >>> the agent_id for a given account number. Example:
    >>>
    >>> Account Number Agent ID
    >>> 1000 1
    >>> 1000 2
    >>> 1100 1
    >>> 1200 1
    >>> 1200 2
    >>> 1200 3
    >>>
    >>> When I add a new agent to account number 1200, I would like it to come
    >>> up automatically with 2 for the agent_id.. I know I could do a select
    >>> on account number and return MAX of agent_id. I could then increment
    >>> that value and use that pair for new agent creation. However, I wonder
    >>> if thee is a way to do that automatically in SQL?
    >>>
    >>> Shelly
    >>>

    >>
    >>

    >
    >




    --
    Mark Rae
    ASP.NET MVP
    http://www.markrae.net
    Mark Rae [MVP], Sep 22, 2007
    #9
  10. "Shelly" <> wrote in message
    news:...

    > Like I said, I know I can do this (adding, of course, the clause WHERE
    > accountNumber=theaccountNumber) , but my question is can SQL do this
    > automatically on the insert statement into the Agent table? If it can,
    > then I can do an ExecuteScalar and return that value from the insert query
    > without having to do two queries in succession with the possiblity of a
    > race condition where someone else is adding an agent to the same account
    > at the same time.


    Do you mean something like this...?

    <insert the record into the first table>
    INSERT Agent (ID, field2, field3,...) VALUES (@@IDENTITY, value2,
    value3,...)


    --
    Mark Rae
    ASP.NET MVP
    http://www.markrae.net
    Mark Rae [MVP], Sep 22, 2007
    #10
  11. Shelly

    Shelly Guest

    "Mark Rae [MVP]" <> wrote in message
    news:OyZR2JP$...
    > "Shelly" <> wrote in message
    > news:...
    >
    >> Like I said, I know I can do this (adding, of course, the clause WHERE
    >> accountNumber=theaccountNumber) , but my question is can SQL do this
    >> automatically on the insert statement into the Agent table? If it can,
    >> then I can do an ExecuteScalar and return that value from the insert
    >> query without having to do two queries in succession with the possiblity
    >> of a race condition where someone else is adding an agent to the same
    >> account at the same time.

    >
    > Do you mean something like this...?
    >
    > <insert the record into the first table>
    > INSERT Agent (ID, field2, field3,...) VALUES (@@IDENTITY, value2,
    > value3,...)


    To get this to work do I have to make the ID a primary key? If so, that is
    not what I want because I want to allow duplicate values -- just not
    duplicate accountNumber-ID pairs. I will try this, though , without making
    it primary and will see what happens.

    Shelly
    Shelly, Sep 23, 2007
    #11
  12. "Shelly" <> wrote in message
    news:...

    >> Do you mean something like this...?
    >>
    >> <insert the record into the first table>
    >> INSERT Agent (ID, field2, field3,...) VALUES (@@IDENTITY, value2,
    >> value3,...)

    >
    > To get this to work do I have to make the ID a primary key?


    Not at all - this is a common misconception. Identity fields do not *have*
    to be their table's primary key...

    > If so, that is not what I want because I want to allow duplicate values --
    > just not duplicate accountNumber-ID pairs.


    Ah - that won't work...

    Every time you insert a record into a table with an identity field, the
    identity field *will* increment - no way round that...

    In this scenario, you would make the identity field the primary key and
    create a unique key based on the two fields...


    --
    Mark Rae
    ASP.NET MVP
    http://www.markrae.net
    Mark Rae [MVP], Sep 23, 2007
    #12
  13. Shelly wrote:
    > This is more of an SQL question, but I would like to know how to do it in
    > SQL Server.
    >
    > In a given table I have two columns of importance. The first is account
    > number (and it is a foreign key). The other is agent_id. The combination
    > must be unique. What I would like to do is to autoincrement the agent_id
    > for a given account number. Example:
    >
    > Account Number Agent ID
    > 1000 1
    > 1000 2
    > 1100 1
    > 1200 1
    > 1200 2
    > 1200 3
    >
    > When I add a new agent to account number 1200, I would like it to come up
    > automatically with 2 for the agent_id.. I know I could do a select on
    > account number and return MAX of agent_id. I could then increment that
    > value and use that pair for new agent creation. However, I wonder if thee
    > is a way to do that automatically in SQL?
    >
    > Shelly
    >


    There is no way that you can auto increment the agent id in that way.

    If you make the agent id an identity field, it would increment for every
    record. That would make it unique, so for every account number the agent
    id:s would also be unique, only not continuous.

    Do you really need them to be continuous? That is a requirement that is
    normally not put on an identity, as it's hard to maintain if you also
    want to be able to delete records.

    --
    Göran Andersson
    _____
    http://www.guffa.com
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Sep 23, 2007
    #13
  14. Shelly

    Alan Silver Guest

    In article <>, Shelly
    <> writes
    >my question is can SQL do this automatically on the insert statement
    >into the Agent table? If it can, then I can do an ExecuteScalar and
    >return that value from the insert query without having to do two
    >queries in succession with the possiblity of a race condition where
    >someone else is adding an agent to the same account at the same time.


    You don't need two queries, nor do you need to worry about racing. You
    use a composite SQL query like this...

    begin transaction

    declare @maxagentid int

    select @maxagentid=max(agentid) from agents where...

    insert agents (agentid,...) values (@maxagentid,...)

    commit transaction

    Bear in mind that this can all go in one string from your ASP.NET code,
    just separate the statements with commas...

    string sql = "begin transaction; declare @maxagentid int; ...";

    Note that this was written off the top of my head, so may contain some
    errors, plus it will need to be modified to your exact situation, but
    hopefully it should be clear enough to show what I meant.

    HTH

    --
    Alan Silver
    (anything added below this line is nothing to do with me)
    Alan Silver, Sep 24, 2007
    #14
  15. Shelly

    Shelly Guest

    "Alan Silver" <> wrote in message
    news:...
    > In article <>, Shelly
    > <> writes
    >>my question is can SQL do this automatically on the insert statement into
    >>the Agent table? If it can, then I can do an ExecuteScalar and return
    >>that value from the insert query without having to do two queries in
    >>succession with the possiblity of a race condition where someone else is
    >>adding an agent to the same account at the same time.

    >
    > You don't need two queries, nor do you need to worry about racing. You use
    > a composite SQL query like this...
    >
    > begin transaction
    >
    > declare @maxagentid int
    >
    > select @maxagentid=max(agentid) from agents where...
    >
    > insert agents (agentid,...) values (@maxagentid,...)
    >
    > commit transaction
    >
    > Bear in mind that this can all go in one string from your ASP.NET code,
    > just separate the statements with commas...
    >
    > string sql = "begin transaction; declare @maxagentid int; ...";
    >
    > Note that this was written off the top of my head, so may contain some
    > errors, plus it will need to be modified to your exact situation, but
    > hopefully it should be clear enough to show what I meant.
    >
    > HTH
    >
    > --
    > Alan Silver
    > (anything added below this line is nothing to do with me)


    Yes, it is clear and a good suggestion. How would you increment the
    @maxagentid by one before (or in) the insert line in the same sql
    transaction?

    Shelly
    Shelly, Sep 24, 2007
    #15
  16. Shelly

    Alan Silver Guest

    In article <>, Shelly
    <> writes
    <snip>
    >Yes, it is clear and a good suggestion. How would you increment the
    >@maxagentid by one before (or in) the insert line in the same sql
    >transaction?


    Sorry, forgot that bit! It is very simple...

    insert agents (agentid,...) values (@maxagentid + 1,...)

    Simple eh? Note that I haven't tested this, but if it doesn't work, you
    can just modify the @maxagentid value before this line.

    HTH

    --
    Alan Silver
    (anything added below this line is nothing to do with me)
    Alan Silver, Sep 24, 2007
    #16
  17. Shelly

    Shelly Guest

    "Alan Silver" <> wrote in message
    news:...
    > In article <>, Shelly
    > <> writes
    > <snip>
    >>Yes, it is clear and a good suggestion. How would you increment the
    >>@maxagentid by one before (or in) the insert line in the same sql
    >>transaction?

    >
    > Sorry, forgot that bit! It is very simple...
    >
    > insert agents (agentid,...) values (@maxagentid + 1,...)
    >
    > Simple eh? Note that I haven't tested this, but if it doesn't work, you
    > can just modify the @maxagentid value before this line.


    This worked great except in one case. That case is where this is the first
    agent to be added for that account. In that case, the first select will not
    find a value for @maxAgentId and the transaction fails. The error message
    is that it cannot insert the value NULL into column 'agentID' in table
    Agent. I guss that this is because the operation of "@maxAgentID + 1"
    failed to consider the NULL of "@maxAgentID to be a zero and so the
    operation failed.

    To solve this I added a line "IF @maxAgentID IS NULL @maxAgentID = 0"
    before the insertion. It now works in all cases.

    Thank you very much.

    Shelly
    Shelly, Sep 25, 2007
    #17
  18. "Shelly" <> wrote in message
    news:...

    > To solve this I added a line "IF @maxAgentID IS NULL @maxAgentID = 0"
    > before the insertion. It now works in all cases.



    Alternatively, you could have used the ISNULL T-SQL function:

    ISNULL(@maxAgentID, 0)


    --
    Mark Rae
    ASP.NET MVP
    http://www.markrae.net
    Mark Rae [MVP], Sep 25, 2007
    #18
  19. Shelly

    Alan Silver Guest

    In article <>, Shelly
    <> writes
    >To solve this I added a line "IF @maxAgentID IS NULL @maxAgentID = 0"
    >before the insertion. It now works in all cases.


    Yup, that's a good way to do it.

    >Thank you very much.


    Pleasure ;-)

    --
    Alan Silver
    (anything added below this line is nothing to do with me)
    Alan Silver, Sep 25, 2007
    #19
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. dna
    Replies:
    1
    Views:
    1,264
  2. William \(Bill\) Vaughn
    Replies:
    0
    Views:
    437
    William \(Bill\) Vaughn
    Aug 21, 2003
  3. David Browne
    Replies:
    0
    Views:
    448
    David Browne
    Aug 21, 2003
  4. ecoolone
    Replies:
    0
    Views:
    743
    ecoolone
    Jan 3, 2008
  5. Jules
    Replies:
    6
    Views:
    139
    Jules
    Jul 15, 2003
Loading...

Share This Page