An SQL question | | |
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
| | | | re: An SQL question
Well, to get the lastAgentID number you could
Select max(AgentID) From Agent
"Shelly" <sheldonlg.news@asap-consult.comwrote in message
news:13f7ktc6d18f0f1@corp.supernews.com... Quote:
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
>
| | | | re: An SQL question
"Just Me" <news.microsoft.comwrote in message
news:Ogr6xxF$HHA.1900@TK2MSFTNGP02.phx.gbl... Quote:
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 Quote:
>
>
>
>
>
"Shelly" <sheldonlg.news@asap-consult.comwrote in message
news:13f7ktc6d18f0f1@corp.supernews.com... Quote:
>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
>>
>
>
| | | | re: An SQL question
OK, now I understand what you are trying to acheive. I think the answer is
no there is not.
"Shelly" <sheldonlg.news@asap-consult.comwrote in message
news:13f7ogiof5fdfee@corp.supernews.com... Quote:
>
"Just Me" <news.microsoft.comwrote in message
news:Ogr6xxF$HHA.1900@TK2MSFTNGP02.phx.gbl... Quote:
>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
> Quote:
>>
>>
>>
>>
>>
>"Shelly" <sheldonlg.news@asap-consult.comwrote in message
>news:13f7ktc6d18f0f1@corp.supernews.com... Quote:
>>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
>>>
>>
>>
>
>
| | | | re: An SQL question
"Shelly" <sheldonlg.news@asap-consult.comwrote in message
news:13f7ogiof5fdfee@corp.supernews.com... Quote:
>
"Just Me" <news.microsoft.comwrote in message
news:Ogr6xxF$HHA.1900@TK2MSFTNGP02.phx.gbl... Quote:
>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
> Quote:
>>
>>
>>
>>
>>
>"Shelly" <sheldonlg.news@asap-consult.comwrote in message
>news:13f7ktc6d18f0f1@corp.supernews.com... Quote:
>>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 | | | | re: An SQL question
In article <13f7ogiof5fdfee@corp.supernews.com>, Shelly
<sheldonlg.news@asap-consult.comwrites Quote:
>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) | | | | re: An SQL question
"Alan Silver" <alan-silver@nospam.thanx.invalidwrote in message
news:nE8L1WezW89GFwPM@nospamthankyou.spam... Quote:
In article <13f7ogiof5fdfee@corp.supernews.com>, Shelly
<sheldonlg.news@asap-consult.comwrites Quote:
>>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 | | | | re: An SQL question
In article <13ffia6ichkfua0@corp.supernews.com>, Shelly
<sheldonlg@asap-consult.comwrites
<snip> Quote:
>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) | | | | re: An SQL question
"Alan Silver" <alan-silver@nospam.thanx.invalidwrote in message
news:KomAeskZV+9GFwIT@nospamthankyou.spam... Quote:
In article <13ffia6ichkfua0@corp.supernews.com>, Shelly
<sheldonlg@asap-consult.comwrites
<snip> Quote:
>>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 | | | | re: An SQL question
"Shelly" <sheldonlg.news@asap-consult.comwrote in message
news:13fi2a2444hu4f5@corp.supernews.com... Quote:
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 | | | | re: An SQL question
In article <13fi2a2444hu4f5@corp.supernews.com>, Shelly
<sheldonlg.news@asap-consult.comwrites Quote:
>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. Quote:
>Thank you very much.
Pleasure ;-)
--
Alan Silver
(anything added below this line is nothing to do with me) |  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,295 network members.
|
