Will the alphabetic prefix increment as well? e.g. PR999, PS000
asked
Auto increment for a Varchar Column
At below I have written a script that will create a table Test and will insert 100 values. CREATE TABLE dbo.Test(PRODID INT,PRODNAME varchar(30)) declare @PRODID int=1 while @PRODID
> @skpani wrote: > At below I have written a script that will create a table Test and will insert 100 values. > The following will produce exactly the table and table contents that you asked for and is very similar to what Father Jack first posted on this thread except that it also makes the table on-the-fly and it uses the "PR" prefix that you wanted. I realize that it's only for 999 rows but it will do so in a very high speed, set based manner rather than using any type of explicit loop or the slowness of an rCTE that counts. SELECT TOP (999) ProdID = CAST('PR'+RIGHT('000'+CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(3)),3) AS VARCHAR(30)) , ProdName = 'Thread' INTO dbo.Test FROM master.sys.all_columns ac1 CROSS JOIN master.sys.all_columns ac2 ; If what you really wanted is a method for the table to automatically increment the ProdID column, there are several methods available depending on how you want it done. The easiest method is to simply make a calculated column. This method was first suggested on this thread by Mister Magoo but you needed to make the realization that you needed to change the formula a bit to get exactly what you wanted. Here's the table code for that method. It uses what is known as a "calculated" column in conjunction with an IDENTITY column. CREATE TABLE dbo.Test ( ProdID AS CAST('PR'+RIGHT('000'+CAST(RowNum AS VARCHAR(3)),3) AS VARCHAR(30)) PERSISTED ,ProdName VARCHAR(30) ,RowNum INT IDENTITY(1,1) CHECK (RowNum
CREATE TABLE dbo.Test(PRODID INT,PRODNAME varchar(30)) declare @PRODID int=1 while @PRODID<= 100 begin insert dbo.Test values(@PRODID,'THREAD'**+CAST(@PRODID as varchar)**) set @PRODID+=1 end select * from dbo.Test drop table dbo.Test
@bhanupratapsngh9 I think you haven't got my question properly.I know what you have done but PRODNAME is dependent on PRODID which I don't want be.Ok just omit the PRODNAME column means we have only PRODID column.Increment this
column.My output must be like this:'PR001' 'PR001', 'PR003
I think you are explaining that you need something like this: USE [tempdb] GO DECLARE @Table TABLE (ProdID varchar(5), ProdName varchar(100)) INSERT @Table ([ProdID], [ProdName]) SELECT TOP 200 RIGHT(REPLICATE('0', 5) + CAST(ROW_NUMBER() OVER (ORDER BY [C].[object_id]) AS varchar(10)), 5) AS Prod_ID, 'THREAD' FROM [sys].[columns] AS C SELECT * FROM @Table AS T In this code we concatenate a fixed string of '00000' to the ID and then use the RIGHT function to return a string that is the required ID plus a prefix of the desired number of '000'
Here is a sample table definition that includes an incrementing char primary key. set QUOTED_IDENTIFIER on; create table IncrementingChar ( ident int identity(0,1) not null, ident_char as (char(65+((ident/26000) % 26))+char(65+((ident/1000) % 26))+RIGHT('000'+CAST(ident as varchar(1000)),3)) persisted primary key );
Thanks to all for your valuable answers. @Fatherjack and @Jeff Moden hat's off to you both .
18 People are following this question.
