December 21, 2012 at 9:49 am
I need to add a column to my existing table that counts up with every record added. But I want it to be something along these lines = ABC000001. Do I need to have one column as just a counter up and how do I create that column?
December 21, 2012 at 9:53 am
ncurran217 (12/21/2012)
I need to add a column to my existing table that counts up with every record added. But I want it to be something along these lines = ABC000001. Do I need to have one column as just a counter up and how do I create that column?
I would recommend NOT doing an incremental value that is a varchar like you are asking for. It will cause you nothing but grief. What is wrong with just using an identity column? It will generate an incremental number and it requires no effort on your part.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2012 at 9:55 am
This is not a good idea. It's best to use an identity for auto-incrementing numbers.
This has been covered here quite recently, & the consensus seems to be that you should update the record after creation with the stub + incremented number.
December 21, 2012 at 9:57 am
laurie-789651 (12/21/2012)
This is not a good idea. It's best to use an identity for auto-incrementing numbers.This has been covered here quite recently, & the consensus seems to be that you should update the record after creation with the stub + incremented number.
I remember that thread. I posted an example of using a computed column for that. But I can't seem to find it. I guess I post too much because it is off my recent posts list already.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2012 at 9:58 am
Ok I have never used an IDENTITY column or created one. What would be the SQL command to create that column?
And are you also saying I should not have a column to make it look like this: ABC000001?
It is mainly to create a confirmation number or ticket number.
December 21, 2012 at 10:14 am
ncurran217 (12/21/2012)
I need to add a column to my existing table that counts up with every record added. But I want it to be something along these lines = ABC000001. Do I need to have one column as just a counter up and how do I create that column?
counts up with every record added , what is that mean ?
if you are looking for something like identity, and represent it as an order no; then why not handle it in application..
give you an example :
A patient MRN (Medical Record Number) is something like Facility123456 :
so the number is stored in a table and Facility in another table ; then representation changed in application..
For the Solution of your problem; I think you better check out how the computed column works .
http://msdn.microsoft.com/en-us/library/ms191250(v=sql.105).aspx
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
December 21, 2012 at 10:15 am
ncurran217 (12/21/2012)
Ok I have never used an IDENTITY column or created one. What would be the SQL command to create that column?
Here is an example of using identity.
create table #IdentityExample
(
ExampleID int identity,
SomeValue varchar(100)
)
insert #IdentityExample (SomeValue)
values ('You can''t insert into an identity column')
,('(Well, there is a way but by default it will not work.)')
,('But the column will populate automatically.')
select * from #IdentityExample
And are you also saying I should not have a column to make it look like this: ABC000001?
It is mainly to create a confirmation number or ticket number.
Yes that is exactly what I am saying. This will be a complete pain to deal with. Is there some reason your confirmation number can't be numeric? You can set the initial value of your identity to whatever you want. You can also set the step value.
create table #IdentityExample
(
ExampleID bigint identity(42345678, 1), --You can even make the step value a negative number if you want
SomeValue varchar(100)
)
insert #IdentityExample (SomeValue)
values ('You can''t insert into an identity column')
,('(Well, there is a way but by default it will not work.)')
,('But the column will populate automatically.')
select * from #IdentityExample
drop table #IdentityExample
Now to do the same thing and make it into a varchar like 'ABC00001' is a real pain. What happens when you get to 'ABC99999'? What is the next value?
Here is an example of doing this using a computed persisted column but you can't add more than 100 rows and you have to figure out how to deal with the ABC. Beginning to see how much a hassle this is?
create table #IdentityExample
(
ExampleID bigint identity(1, 1),
SomeValue varchar(100),
ConfirmationNumber AS 'ABC' + right(replicate('0', 2) + cast(ExampleID as varchar(8)), 8) persisted
)
insert #IdentityExample (SomeValue)
values ('You can''t insert into an identity column')
,('(Well, there is a way but by default it will not work.)')
,('But the column will populate automatically.')
select * from #IdentityExample
drop table #IdentityExample
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2012 at 10:33 am
Yea you are right, I will leave it just as the Identity column. Thanks again!
December 21, 2012 at 1:34 pm
If you do need a prefix (and/or suffix), you can do this:
CREATE TABLE dbo.tablename (
prefix char(3) DEFAULT 'ABC',
ident int IDENTITY(1, 1) NOT NULL,
id_col AS prefix + CAST(ident AS varchar(10)) --PERSISTED,
--OR, if you want the result to be fixed-length, zero-filled, like ABC0000000001:
-- id_col AS prefix + RIGHT(REPLICATE('0', 10) + CAST(ident AS varchar(10)), 10) --PERSISTED,
--...
The computed column, in this case "id_col", is used as the actual identifying column.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply