SQLTeam.com | Weblogs | Forums

Insert into and increment


#1

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

#2

Try:


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


#3

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?


#4

This should at least be close:

/* --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

#5

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.


#6

I modified my create table statement to include

ID int Identity (1,1) not null

and then I added

DBCC CheckIdent ('##Random.ID', RESEED, @maxint)

(I made some changes to my code, @maxint is the last two digits of my max log from another table converted to int)

The problem is I get an error that says:

Cannot find a table or object with the name "##Random.ID". Check the system catalog.

The object does exist so I am not sure what the problem is.


#7

DBCC CheckIdent ('tempdb.##Random.ID', RESEED, @maxint)


#8

same result


#9

No column name needed so it is just

DBCC CheckIdent ('##Random', RESEED, @maxint)