I am creating an application that selects ten random customers from a table and writes them to a temp table and displays them for the user who then will click a button to write them to a final table (log). When they are written to that table I want it to find the max value in that table in the format xx-xxxxxxx where xx is the year and xxxxxxx is a number that will increment by 1 for each row written to the table. So if the current max log_no in that table is 16-0000010 then next one written will be 16-0000011, 16-0000012, and so on to 16-0000020. I have the following code that converts the data to an integer and increments it and then converts it back but I am not sure of the best way to do this so it increments for each row. I can't add the row number because when the year changes I want the number to reset to 17-0000001. Any suggestion is appreciated.
> IF Object_ID('tempdb..##Random') is not null
> DROP TABLE dbo.##Random
> CREATE TABLE ##Random
> (
> my columns
> )
> DECLARE @maxlog varchar(100)
> DECLARE @SeqNo varchar(20)
> DECLARE @Year varchar(2)
> SET @maxlog = (select right('00000000000000'+ convert(varchar(7),right(max(right(logSeq_num, 7)),2) + 1), 7) FROM log)
> set @Year = (select right(datepart(yy, getdate()),2))
> set @seqNo = @Year+'-'+@maxlog
> INSERT INTO ##Random
> SELECT TOP 10 columns
> FROM tables
> WHERE
> ORDER BY NEWID()
> END
USE [tempdb]
GO
IF Object_Id(N'dbo.Jic', 'U') IS NOT NULL
DROP TABLE dbo.Jic;
GO
CREATE TABLE [dbo].[jic](
[ID] [int] IDENTITY(1,1) NOT NULL,
[jicno] AS (((right(datepart(year,getdate()),(2))+'-')+left('0000000000',(10)-len([ID])))+CONVERT([varchar](10),[id])),
[JicName] [varchar](20) NULL
) ON [PRIMARY]
GO
INSERT dbo.jic
( JicName )
VALUES
( 'Joe' )
, ( 'John' )
, ( 'Jane' )
, ( 'Jim' );
GO
SELECT ID
, jicno
, JicName
FROM dbo.jic;
GO
DROP TABLE dbo.jic;
GO
Thanks Jo - I am assuming that I can set the start position of the identity column to a variable which would be max(log_no) so each time the table is created with the next incremental number, correct?
Any idea how to handle resetting it to 0000001 on January 1st?
/* --sample data
create table #log ( logSeq_num varchar(20) )
insert into #log values('16-0000010')
create table #random ( col1 varchar(20) )
insert into #random values('abc'),('def'),('ghi')
*/
--INSERT INTO dbo.log ( ... )
SELECT r.*,
log_max.logSeq_num_prefix + RIGHT(REPLICATE('0', logSeq_num_suffix_length) +
CAST(CAST(log_max.logSeq_num_suffix AS int) + r.row_num AS varchar(20)), logSeq_num_suffix_length) AS logSeq_num_new
FROM (
SELECT *, ROW_NUMBER() OVER(ORDER BY NEWID()) AS row_num
FROM #Random
) AS r
CROSS JOIN (
SELECT LEFT(max_logSeq_num, 3) AS logSeq_num_prefix,
SUBSTRING(max_logSeq_num, 4, 20) AS logSeq_num_suffix,
LEN(SUBSTRING(max_logSeq_num, 4, 20)) AS logSeq_num_suffix_length
FROM (
SELECT MAX(logSeq_num) AS max_logSeq_num
FROM #log WITH (TABLOCK, UPDLOCK)
WHERE logSeq_num LIKE RIGHT(YEAR(GETDATE()), 2) + '%'
) AS derived
) AS log_max
That's correct.
But if you use a permanent table as a staging table, the identity would just keep incrementing. You could delete if you want it to keep incrementing or truncate to reset to original start number.
