«

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

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
>
>

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
>

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

"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

"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
>
>

"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
>>
>
>

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
>>>
>>
>>
>
>

"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

"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