Insert with Next Customized Sequence (T-SQL)


--CREATE TABLE vendor_trans(vendor_cd char(3),trans_cd char(2)
--)
--INSERT INTO [dbo].[vendor_trans]
--            ([vendor_cd],
--             [trans_cd])
--VALUES      ( 'ABC','00' ),
--            ( 'ABC','01' ),
--            ( 'ABC','02' ),
--            ( 'ABC','03' ),
--            ( 'ABC','04' ),
--            ( 'ABC','05' )
----,   ( 'ABC' ,'ZY' )

 




--Prepare the custom sequence table

CREATE TABLE #l  (letter CHAR(1)) 
INSERT INTO #l values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z') 
 
CREATE TABLE #n  ( number INT ) 
INSERT INTO #n Values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
  
 
 SELECT id  into #mySequence
        FROM   (SELECT Cast(a.number AS CHAR(1)) + Cast(b.number AS CHAR(1)) AS id
                FROM   #n a CROSS JOIN #n b 
        UNION ALL
         SELECT Cast(a.number AS CHAR(1)) + Upper(letter) AS id
                FROM   #l CROSS JOIN #n a

				   UNION ALL
         SELECT  Upper(c.letter) + Upper(d.letter) AS id
                FROM   #l c CROSS JOIN  #l d
				
				)  tt        
 




 --Insert vendor row
declare @vendor_id char(3)='BBB'
 
 
 
insert into vendor_trans (vendor_cd,trans_cd)

Select vendor_id, idlag from (
Select @vendor_id as vendor_id,  Lead(id) Over(Order by id) idlag , id from #mySequence) t
Where id= (Select  max([trans_cd])  from vendor_trans
WHERE  vendor_cd = @vendor_id 
Group by [vendor_cd]) and idlag is not null
UNION
SELECT @vendor_id, '00'  
EXCEPT 
Select vendor_cd,trans_cd from vendor_trans  




select * from vendor_trans
order by vendor_cd, trans_cd




 --  drop table vendor_trans 
 drop table  #mySequence
 DROP TABLE #n, #l 
 

 

http://social.msdn.microsoft.com/Forums/en-US/433c2edd-da52-4beb-8673-3fdd5dc87f20/generating-next-value-in-a-custom-2-character-sequence?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s