 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / ASP.NET / General / September 2007An 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 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. [quoted text clipped - 19 lines] > > 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 a.s 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. [quoted text clipped - 28 lines] >> >> 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 a.s 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 [quoted text clipped - 3 lines] > > 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. [quoted text clipped - 28 lines] >>> >>> 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, [quoted text clipped - 25 lines] >> >> Shelly Well, to get the lastAgentID number you could Select max(AgentID) From Agent > This is more of an SQL question, but I would like to know how to do it in > SQL Server. [quoted text clipped - 19 lines] > > 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 >> This is more of an SQL question, but I would like to know how to do it in >> SQL Server. [quoted text clipped - 19 lines] >> >> Shelly OK, now I understand what you are trying to acheive. I think the answer is no there is not. >> Well, to get the lastAgentID number you could >> [quoted text clipped - 33 lines] >>> >>> Shelly >> Well, to get the lastAgentID number you could >> [quoted text clipped - 33 lines] >>> >>> Shelly  SignatureMark Rae ASP.NET MVP http://www.markrae.net > 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 [quoted text clipped - 3 lines] > 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,...) SignatureMark Rae ASP.NET MVP http://www.markrae.net >> 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 [quoted text clipped - 9 lines] > 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 >> Do you mean something like this...? >> [quoted text clipped - 3 lines] > > 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... SignatureMark Rae ASP.NET MVP http://www.markrae.net >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 SignatureAlan Silver (anything added below this line is nothing to do with me) >>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 [quoted text clipped - 25 lines] > > 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 <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 SignatureAlan Silver (anything added below this line is nothing to do with me) > <snip> >>Yes, it is clear and a good suggestion. How would you increment the [quoted text clipped - 7 lines] > 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 > 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) SignatureMark Rae ASP.NET MVP http://www.markrae.net >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 ;-) SignatureAlan Silver (anything added below this line is nothing to do with me) > This is more of an SQL question, but I would like to know how to do it in > SQL Server. [quoted text clipped - 19 lines] > > 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. SignatureGöran Andersson _____ http://www.guffa.com
Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.