Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Hi All,I need to set up a kind of identity insert that gives an output in the format: 00001, 00002, 00003 etc. Is there a formatting option for this sort of output using normal identity insert features or do I need to write a function to insert these values (perhaps as text) each time a new record is created?Sorry if this is really simple but it's only my 4th day in this job!Marcha
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts
Posted - 2006-08-10 : 09:02:39
It will be better if you use IDENTITY column...do all formatting (like padding zeros in the begining) at the front-end level if possible.Harsh AthalyeIndia."Nothing is Impossible"
marchawinslet
Starting Member
11 Posts
Posted - 2006-08-10 : 09:42:32
Thanks Harsh, I thought of this first of all but the problem I have is that other people wil access the table from their own applications and I really need everyone to see a single unified view of the data without the need for formatting instructions.Any other ideas?
khtan
In (Som, Ni, Yak)
17689 Posts
Posted - 2006-08-10 : 10:07:42
use a computed column to format the zero padding
create table #table( id int identity(1,1), key_id as right('00000' + convert(varchar(5), id), 5), col int)insert into #table (col)select 10select * from #table/*id key_id col ----------- ---------- ----------- 1 00001 10*/
KH
marchawinslet
Starting Member
11 Posts
Posted - 2006-08-10 : 11:25:29
Thanks KHTan, that's just what I needed!
madhivanan
Premature Yak Congratulator
22864 Posts
Posted - 2006-08-10 : 12:11:56
Use only identity column. If you use front end application, use format function to format the numberMadhivananFailing to plan is Planning to fail