An SQL question

[Shelly]

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]

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,
....
)

 

[Just Me]

Well, to get the lastAgentID number you could

Select max(AgentID) From Agent

 

[Just Me]

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 !

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,
....
)

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]

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,
....
)

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]

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.

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,
....
)

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]

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

 

[Just Me]

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

 

[Mark Rae [MVP]]

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

 

[Mark Rae [MVP]]

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,...)

 

[Shelly]

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

 

[Mark Rae [MVP]]

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

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

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.

 

[Alan Silver]

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

 

[Shelly]

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

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

 

[Alan Silver]

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

 

[Shelly]

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

 

[Mark Rae [MVP]]

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)

 

[Alan Silver]

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