How to auto increment with character + integer?

Hi,

Can anyone help me? I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.

Here is my question, how can I do in the following format:

"PIC0001", "PIC0002", "PIC0003", ......... etc.

Anyone can help me?

Thanks.You can create a calculated field as part of a view, using the number from the IDENTITY column appended to the string "PIC".

SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID|||i'm a rookie...can u explain more detail for this one pls? SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID|||SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID

'PIC' is the literal you apparently want at the beginning of the string. ID would be a column of type INT, set to IDENTITY. REPLACE(STR(ID,4),' ','0') takes an int number, like 12, and changes it to '0012', thus for a row with an ID of 12, you would get a column called AlphaID with a value of 'PIC0012'.|||hello
thank you for the support

you mean that i have to make two fields one called ID of type int and the other called alphaid
of type nchar and put the default value of this field as you say?

or i have to put the statment SELECT 'PIC' + REPLACE(STR(ID,4),' ','0') AS AlphaID in the code of the application?

thank you again for the help|||If the prefix is ALWAYS 'PIC', then I would just create a VIEW that has as part of it that SELECT statement. If it varies, then I would create the table with the ID field and the Prefix field, and then combine them in a view.|||You could also implement this as a computed column within the tabl itself.

This approach might add more overhead though, so it depends on how many of your selects would actually be accessing this field directly.

One potential advantage to having the value as a computed column is that, assuming the computed column meets certain criteria, you can index the column. If you are planning on retrieving rows based on this field, this could work to your advantage.|||Actually, given triggers, you could certainly create a "real" field to hold the value and have it populated in the trigger.|||But would you gain anything by going the trigger route? Wouldn't that just add overhead to your inserts? Or would the overhead be comparable to the overhead assiociated with a computed column (at least when maintaining an index on that computed column).

To my mind, it is a LOT easier to configure a computed column for this solution than it is to program a trigger. But, then again, I rarely use triggers, so I might not be the best resource here. All things being equal though (and I'm NOT saying they are; you might have a better idea than me on that one), I'd go with the solution that's easier to implement.|||Presuming the ID number part of the computed column never changes, you would need only an INSERT trigger. In most applications, the number of INSERTs vs. the number of SELECTs is very small, and so the overhead of doing the insert trigger is really small.

I certainly agree that often easier is better. I have not compared performance of these two solutions (though I would likely do a column fed by a trigger).|||I might suggest you use IDENTITY as PK and add 'PIC' and '000..' to the layer(s) that require it.

This is based on the presumption that PIC000n... is a (surrogate) PK with no "real" business meaning and using only int will make the key more efficient.|||I agree, to an extent. If multiple systems, or layers, might be touching this data, then it CAN add some consistency to be able to define this formatting in one central location.

While you could easily add this computed "key" as a property of a class, for example, you might then need to write numerous report which use this key; some might be Crystal reports, some SQL Server reporting services. Some might be generated ad hoc by the end user. IF this is a business-level way of identifying these records, I don't necessarily see a problem with allowing that identifying characteristic to be stored with the record itself.|||hello
thank you for the support
but can you give me more details about how can i do that?

thank you again for th help anf for the time|||Hi,
I have similar question, I have created a table and with a primary key called "ID".
However, I want the "ID" be auto increment as well.
Here is my question, how can I do in the following format:
"1", "2", "3", ......... etc.
Anyone can help me?
Thanks.|||If you just need numbers to autoincrement, set the column to be an IDENTITY column.

How to auto increment an alphanumeric Primary Key in SQL? :(

How to auto increment an alphanumeric Primary Key in SQL? :( Because I want to add something like this in the Primary Key, for example i'll add a new data with an alphanumeric value of ABC-0001, then I'll add another 1, and it auto increments to ABC-0002 and so on.. How can I do it? And if I'll add a new alpha character in the Primary Key field, for example DEF-0001, then I'll add another and it auto increments to 002, and so on, and it will go back to 0001 if i'll use another combination of alpha characters. for example, i'll add a new alpha character AAA, will it go back to 0001 or it will continue?

T___T I hope u get my point.. I want my table to look like this if i have added the data

ABC-0001
ABC-0002
DEF-0001
DEF-0002
AAA-0001

then if il add a new 1, for example ABC

it will auto increment to 0003 in the same field, it will look like this after adding

ABC-0001
ABC-0002
ABC-0003
DEF-0001
DEF-0002
AAA-0001

Will it be possible? :(

hi,

i got wat you wanted to say.but i think auotincrement is possible only with numeric fields,i cant think of any possible algorithm that would solve your problem for autoincrementing alphanumeric field.But one possible solution would be to generate your own primary key.For that to happen first you must split your Alpha-numeric field into two..ie first part will take ABC and second would take ur numeric field 0001 etc...

make your primary key as the combination of both the fields...this is the solution i could think of...

hope you could find,wat you actually wanted..all the best :)

|||

Hey ranjit that's what im thinking off also... bat what if there's a new alpha combination, for example DEF, it will continue to autoincrement, and i think it will not return to 0001.. so what will happen is, if i have added ABC-0001 and ABC-0002, the next one would be
DEF-0003 not DEF-0001

will it be possible to put it back to 0001 if il add a new alpha combination? or very impossible? :(

T___T

|||

to get the primary key... you have to findmax for that perticular string with Query sequence like...

SELECT MAX(CAST(SUBSTRING(id,CHARINDEX('-',id)+1,50) AS INT)) + 1 FROM TBL_Table WHEREID LIKE 'DEF%' [this can be like ..."id like 'ABC%' to get highest value in ABC Sequence].. hope it helps you..

|||

wow thanks for this kaushal.. il try it out :)

How To Auto Increment A Primary ID Field?

I was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?

For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input......I'm very sorry but I'm having a hard time putting this into words for some reason..

umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?

Thanks

Adam.

On the ID column set identity=Yes, and the value will be assigned automatically!

HTH

|||OH MAN your joking right? LOL I must have overlooked something.....because at first I thought that was how you do it but I was not sure and I think I tried setting it to yes in the past but I must have overlooked the values that changed or something....maybe I was having an off day.....but thats perfect thank you so much...|||Actually sorry to bother you again by any change would you know if you can set a primary key and a foreign key inside the same table?|||

This is why I needed my foregn key and primary key things to work...well at least my search's that I do point me in that direction.

I have a couple of listboxs where a user clicks on an option in parent listbox and the results come out on the child list box

Pritty much my problem is that when I use one table and several columbs the first ListBox (ListBox1) which only contained 6 Items had about 30 more Blank (Or Database NULL) fields after the 6 Selections....This was due because some fields were longer then others......The only way I've got around this is to create a different sql datasource for all selections which gives me 7 total sqldatasource on one particular page.....not a very smart coding practise lol...

How to assign primary key or forign key to existing tables

Hi,

I am new to SQL server. I have one doubt.

I am using SQL server 2005 Express Edition

I have two tables which I created earlier. Now I need to coonect these tables using primary key and foreign key.
So how can I assign these PK and Fk to these tables?
Thanks in advance.
Siju George

Quote:

Originally Posted by sijugeo

Hi,

I am new to SQL server. I have one doubt.

I am using SQL server 2005 Express Edition

I have two tables which I created earlier. Now I need to coonect these tables using primary key and foreign key.
So how can I assign these PK and Fk to these tables?
Thanks in advance.
Siju George

Hi
You can do by searching in sql books online.
Ok......add primary key constraint on one column in one table using alter table add constraint.add refference constraint on one column in other table
example:
[code]

CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE) ;
GO
ALTER TABLE doc_exe ADD

-- Add a PRIMARY KEY identity column.
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,

-- Add a column that references another column in the same table.
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),

-- Add a column with a constraint to enforce that
-- nonnull data is in a valid telephone number format.
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' OR
column_d LIKE
'([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),

-- Add a nonnull column with a default.
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081 ;
GO
EXEC sp_help doc_exe ;
GO
DROP TABLE doc_exe ;
GOsql

How to add the NOT NULL and PRIMAY KEY Constraints with ALTER command?

Dear All,

I wanted to alter a column in a table for adding both NOT NULL and PRIMARY KEY constraints to an existing column. How do I go about that.

I need your help.

First use alter table alter column for redefine column with not null, then use alter table add constraint

Code Snippet

createtable MyTable

(

id intnull,

namevarchar(100)

)

altertable MyTable altercolumn id intnotnull

altertable MyTable addconstraint pk_id primarykey(id)

how to add record with same Primarykey that user deleted

I have a sql server database that she deleted a record with ID as 2873. I would like to try to add this record manually, but the primary key can't be edit. How can i add this record with the same ID as she deleted?
Many thanks.An autonumber? Look at IDENTITY_INSERT in BOL.

how to add primary key in existing table

i have table fff .it has two fields one is fno int , another is fname
varchar(20)
fff
fno fname
--- ----
100 suresh
102 ramesh
here there is no not null constraint and identity column then
i am add primary key constraint fno column pls help mesurya (suryaitha@.gmail.com) writes:

Quote:

Originally Posted by

i have table fff .it has two fields one is fno int , another is fname
varchar(20)
fff
fno fname
--- ----
100 suresh
102 ramesh
here there is no not null constraint and identity column then
i am add primary key constraint fno column pls help me

ALTER TABLE fff ADD CONSTRAINT pk_fff PRIMARY KEY (fno)

You find the syntax for ALTER TABLE in Books Online. Yes, it is quite
complex. Then again, there are quite a few examples in that topic.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to add Fill Factor in the SQL script

I am trying to set up the relationship (Primary Key and Forign Key) in several tables. I would like to find a way also be able to set 'Fill Factor = 90%' in the script. Here is the code that i have so far:

ALTER TABLE Req
ADD CONSTRAINT FK_Req_Bow FOREIGN KEY ( BowID )
REFERENCES Bow ( BowID )

Any help would be greatly appreciated!

J827FILLFACTOR pertains to an index that you need to create for your FOREGN KEY constraint, not to the constraint itself.

how to add autonumber (increment) unique ID to sqlserver

Hi,

I am a newbie learning sql server. I used to use Access table where I set the primary ID (int) as autonumber. Every insert will generate a new ID, which is incremented by 1.

How do I add an INSERT statement to add the incrementing primary ID in sql server. I noticed that you can't set the ID to autonumber as you do in Access. I read that you can set the ID as identity, which can auto increment.

How do I handle this in the INSERT cmd. The only thought is to call a sql cmd with a max on the current id and increment by 1 in vbscript. Afterwards, add the increment ID into the INSERT call.

Is there an easier way to do this?

Thanks,

JohnHi John,

Correct. You can't write to an identity field (and I didn't know you could in Access). You don't write to it, so don't include that field in your insert statement.

If you need the new ID number, return the value from the SQL Server SCOPE_IDENTITY function after you insert the row, either by returning it from your stored procedure or immediately after doing the insert. And you can batch them together so that you have only one round trip to the SQL Server server.

And there are lots of ways to do this, depending on the needs of your application.

Is that enough information? If not, just ask. It's pretty straightforward once you get to know SQL Server a bit.

Don