Increment Field Value Automatically
-
I need a way to update and/or increment a field value automatically within SQL table. The table fields are as follows:
Field Value:
LogID int
HTCD char(7) (The field that needs to be incremented, i. e., 0001-04)
CaseTracking char(10)
AgencyTracking char(10)
BarCode char(25), etc.
A user will enter in the HTCD field 0001-04. The 0001 would increment to the next number of 0002. The number after the dash (-) represents the current year 2004. If a user begin inputting into the database on January 1, 2004, the number would be entered as 0001-04 and only the first four number would change everytime a new entry is made until the end of the year December 31, 2004. The last number could be 0465-04. I would like the ability to have the number rollback to 0001 with the year being incremented to the next year (2005).
I'm not sure how to write a SQL statement or stored procedure that would accomplish this task. Please help!
-
SQL Server has IDENTITY to do autonumbers. It can only be used with an int data type, but you could create a computed column to return the identity + year in the format 0000-00.
Here;s some sample code
CREATE TABLE dbo.TableXX ( LogID int NOT null IDENTITY (1, 1), HTCD AS RIGHT('0000' + CAST(LogID as varchar(4)),4) + '-' + RIGHT(CAST(DATEPART(yy,LogDate) as varchar(4)),2), LogDate smalldatetime NOT NULL DEFAULT Getdate(), CaseTracking char(10) ) ON [PRIMARY]insert into tablexx(CaseTracking)values('xyz') insert into tablexx(CaseTracking)values('xxz') insert into tablexx(CaseTracking)values('zzz')select * from tablexx
Julian Kuiters
juliankuiters.id.au -
My suggestion is to create a trigger to do this, as it will survive New Year and start over again on 1. One small flaw with this version of the trigger is that it handles only one row at a time.
Hope this helps.
Best regards,
Vemund Haga
create trigger IncrementTable_CreateHTCD
on IncrementTable after insert
as
-- Declare counter that runs each year
declare @HTCD_ID int
-- Get the highest number for this year
select top 1 @HTCD_ID = left(HTCD, 4)
from IncrementTable
where right(HTCD, 2) = left(convert(varchar(20), getdate(), 2), 2)
order by left(HTCD, 4) desc
-- Check for NULL values, happens for first row each year
if @HTCD_ID is null
set @HTCD_ID = 0
-- Increment the counter by one
set @HTCD_ID = @HTCD_ID + 1
-- Update the table with the new value
update IncrementTable
set HTCD = right('0000' + cast(@HTCD_ID as varchar(4)), 4) + '-' + cast(left(convert(varchar(20), getdate(), 2), 2) as varchar(2))
from IncrementTable IT
inner join inserted I on IT.LogID = I.LogID
-
is this code unique? or does each user start at 0000 each year
if so then you need to make a trigger for insert that uses something along the lines of
update mytable set x=(select convert (char(4),max(convert(integer,left(4,[myfield])))+1))+'-004' from mytable where a=b and c=d and e=f)
MVDBA
-
There will be five individuals inserting data into the table. Each entry should automatically display the next increment number available for input. No user will have their own sequence of numbers.
-
There will be five individuals inserting data into the table. Each entry should automatically display the next increment number available for input. No user will have their own sequence of numbers.
If each entry needs an incrementing number, use an IDENTITY column. IDENTITY columns are like Autonumber in Access, and incremement the number by one for each added row (attempted/committed).
If you must mash the year onto the end of that, add a date to the record, and a computed column to create the resulting number (see my original post above).
Julian Kuiters
juliankuiters.id.au -
There will be five individuals inserting data into the table. Each entry should automatically display the next increment number available for input. No user will have their own sequence of numbers.
I'm not certain, but from your statement above everyone may be missing the point of this. If you have five users and they all need to see the next available input number prior to input, you will run into a concurrency problem when more than one user attempts to save the same number.
-
I'm not certain, but from your statement above everyone may be missing the point of this. If you have five users and they all need to see the next available input number prior to input, you will run into a concurrency problem when more than one user attempts to save the same number.
If you use IDENTITY, you don't insert anything into that column, SQL Server adds the identity number to every inserted row, guaranteeing a UNIQUE number. The user would not need to see the next available number, only after insert can you see the IDENTITY number that was just used with SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
What if you need to show the number? Well insert a empty row, then select @intMyId = SCOPE_IDENTITY() to get the unique number. When you are ready to "save" the data for the record, use an UPDATE statement to update the row WHERE [identity column name] = @intMyId. (or if you are using an ADO record, you could just update the record directly).
IDENTITY columns are designed to auto-increment, without user input. There should be no need for a user to manually enter a number, for exactly the reason you said, two people could enter the same number and screw up the system. IDENTITY columns solve this problem, by getting SQL Server to manage the incrementing at the time of transaction. Even if you had a hundred million people insert a row at once, SQL Server would make sure they all got unique numbers.
Julian Kuiters
juliankuiters.id.au -
To: Julian
If I use the auto-increment within SQL, it will not allow me to add the dash and current year after the generated number. For example, if the number starts off as 1, SQL doesn't know to insert a "-", then the year of 04. This is what I need done. I have a field already called LogID, which is identity field. The next field is where the user would enter 0001-04; 0001 being the first entry in the application followed by the year.
So, if I'm user #1, I want to be able to see 0001-04 already inserted into the field as I enter in new data. When user #2 starts entering new data, I want user #2 to see the next generated number of 0002-04, and so on. The user wants the application to generated this unique number.
Another example I can provide, when a bank issues checks on your account, there are preformatted numbers on the upper right hand corner, i. e., check #1001. Each time you write a check the number increasing by one, making the next check you write #1002. That is how I want the trigger and/or stored procedure to be written. However, I've never written a trigger, so I'm not sure what parameters are needed to complete this task.
I hope this explains what my intend is.
-
how about
create 3 additional fields - year and yearidentifier and autonumber
then create a trigger on the table
create trigger t1 on mytable for insert as
if @rowcount=1
declare @myval integer
set @myval=(select max(cast (yearidentifier as integer))+1 from mytable where year=(select inserted.year))
update mytable set yearidentifier= @myval where mytable.autonumber=(select inserted.autonumber)
update mytable set compositeidentifier=cast(yearidentifier as char(4))+'-'+cast(year as char(3))
you get the result you want, i'm sure someone could refine this a bit, but i'm in a rush.
MVDBA
-
A Computed column would work perfectly. You need the two parts of your number (XXXX-YY) to be columns in your table. XXXX comes from your existing ID, YY would need to come from a datetime column (set default to GetDate()).
Your computed column should then be : HTCD AS RIGHT('0000' + CAST(LogID as varchar(4)),4) + '-' + RIGHT(CAST(DATEPART(yy,LogDate) as varchar(4)),2)
Now, you can't "see 0001-04 already inserted into the field as I enter in new data", because you can't see something that doesn't exist. You need to insert an empty row first, which would populate the columns with their default values.
Try the code below in a testing database, and see what I mean. You won't hit any concurrency issues if you use an Identity field.
By the way: How are your users interacting with the database? Is there a front end app? Access data form? Webpage? or just Enterprise Manager?
CREATE TABLE dbo.TableXX ( LogID int NOT null IDENTITY (1, 1), HTCD AS RIGHT('0000' + CAST(LogID as varchar(4)),4) + '-' + RIGHT(CAST(DATEPART(yy,LogDate) as varchar(4)),2), LogDate smalldatetime NOT NULL DEFAULT Getdate(), CaseTracking char(10) --AgencyTracking char(10) --BarCode char(25), etc. ) ON [PRIMARY]insert into tablexx(CaseTracking)values('xyz') insert into tablexx(CaseTracking)values('xxz') insert into tablexx(CaseTracking)values('zzz')select * from tablexx
Julian Kuiters
juliankuiters.id.au -
To: Julian Kuiters
I'm building a front end application that will be accessed through our intranet with a SQL backend. When the users login, they will be directed to a main form that will allow them to input data.
I noticed the example refers to a CREATE TABLE dbo.TableXX. However, for security reasons, we do not allow users to input directly into a table. So, how would I be able to use your example in a view or stored procedure. I also noticed that on the second line beginning with the HTCD field, you do not indicated what data type this field represents. Is this because you are not required to specify whether it's a varchar, int, char, etc., when you use the expression "AS RIGHT" in your argument?
-
To: Julian
Ye, I tried the SQL statement and it worked the way you said. I'm so happy. (I still need to know if this will work with a View or Stored Procedure. Thanks for all your help.
-
Glad to hear it worked!
in the code the HTCD column doesn't need a datatype because its value is constructed on the fly by SQL Server: the AS RIGHT('0000' + CAST(LogID as varchar(4)),4) + '-' + RIGHT(CAST(DATEPART(yy,LogDate) as varchar(4)),2) tells SQL Server how to calculate the value of this column. (LogID padded with '0's, append a '-', append last two digits of year from LogDate).
Once the table has been constructed like this, you can use the column with a view / sproc without any troubles. Its value is automatically calculated by sql server whenever you select, insert or update a row. So its just like any other table. The only difference is you never need to insert/update data in this column, sql server will look after it for you.
Give it a try. wrap a view / sproc around it.
Julian Kuiters
juliankuiters.id.au
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply