An SQL question

  • Thread starter Thread starter Shelly
  • Start date Start date
S

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
 
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,
....
)
 
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 said:
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
Click to expand...
Click to expand...
 
Satish Itty said:
Make the Agent ID in the table as IDENTITY column and it will
autoincrement itself.
Click to expand...

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
Click to expand...
Click to expand...
 
Just Me said:
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.
Click to expand...

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 !
Click to expand...

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Just Me said:
Well, to get the lastAgentID number you could

Select max(AgentID) From Agent
Click to expand...

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
 
OK, now I understand what you are trying to acheive. I think the answer is
no there is not.
 
Shelly said:
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
Click to expand...
 
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.
Click to expand...

Do you mean something like this...?

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

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

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
 
To get this to work do I have to make the ID a primary key?
Click to expand...

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.
Click to expand...

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...
 
Shelly said:
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
Click to expand...

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.
 
Shelly said:
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.
Click to expand...

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 said:
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
Click to expand...

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
 
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?
Click to expand...

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 said:
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.
Click to expand...

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
 
To solve this I added a line "IF @maxAgentID IS NULL @maxAgentID = 0"
before the insertion. It now works in all cases.
Click to expand...


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

ISNULL(@maxAgentID, 0)
 
Back
Top