62217

Autoincrement of primary key column with varchar datatype in it

Question:

CREATE TABLE Persons ( P_Id varchar(6) NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), PRIMARY KEY (P_Id) )

in this table P_Id is the primary key. We want to generate autoincrement of P_Id with default value (PN00) in the start while inserting only LastName and FirstName .eg :-PN001 for first entry ,PN002 for second,PN003 for third and so on .

Answer1:

The only viable solution is to use

<ul><li>an ID INT IDENTITY(1,1) column to get SQL Server to handle the automatic increment of your numeric value</li> <li>a <strong>computed, persisted</strong> column to convert that numeric value to the value you need</li> </ul>

So try this:

CREATE TABLE dbo.Persons (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, P_ID AS 'PN' + RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED, .... your other columns here.... )

Now, every time you insert a row into Persons without specifying values for ID or P_ID:

INSERT INTO dbo.Persons(Col1, Col2, ..., ColN) VALUES (Val1, Val2, ....., ValN)

then SQL Server will <strong>automatically and safely</strong> increase your ID value, and P_Id will contain values like PN00001, PN00002,...... and so on - automatically, safely, reliably, no duplicates.

Answer2:

There are different ways to address your issue.

<ol><li>You could use a <strong>Trigger</strong>.Triggers are activated on some events. You could create a trigger for 'Instead of Insert On Persons' event. When the event is triggered, then generate a new P_Id. Insert this new P_Id alongwith all the values as the new record for your table.</li> </ol>

This approach wont have coupling with the table's schema.

Refer this link for more information on Triggers : <a href="https://msdn.microsoft.com/en-IN/library/ms189799.aspx" rel="nofollow">https://msdn.microsoft.com/en-IN/library/ms189799.aspx</a>

Refer this link to emulate 'before insert trigger' in SQL Server: <a href="https://stackoverflow.com/questions/15458006/how-to-emulate-a-before-insert-trigger-in-t-sql-sql-server-for-super-subtype" rel="nofollow">How to emulate a BEFORE INSERT trigger in T-SQL / SQL Server for super/subtype (Inheritence) entities?</a>

<ol start="2"><li>

You could also use a <strong>Procedure</strong> like :

create procedure Persons_insert(@lastname varchar(255), @firstname varchar(255)) as begin --write code to generate the ID as you like insert into Persons(p_id,lastname,firstname)values(generated_id,lastname,firstname); end </li> </ol>

Recommend

  • Plot 4 points based on the distance between them?
  • Custom Class used as key in Dictionary but key not found
  • pyparsing a field that may or may not contain values
  • Automatic update of parent record updated_at field (Elixir-Ecto)
  • Can't get WCF service's operations list with Web Service Studio client
  • Doctrine2 entity with varchar id don't insert id into database
  • ON INSERT: Cannot add an entity with a key that is already in use
  • ASP.NET MVC 3 OnActionExecuting causes infinite loop
  • WSO2 as a Service Provider
  • Can I Modify Hibernate/JPA Attribute Dynamically?
  • How to add comma separated string into datatable in c#?
  • Get SignalR User (Hub.Context) outside of hub
  • Centering a specific element among others with flexbox [duplicate]
  • How would you audit ASP.NET Membership tables, while recording what user made the changes?
  • yii2 create translated URLs
  • How to call MoveDataTableCursor in a web test plugin to go through a limited set of rows in a table?
  • Plot a table with R
  • Rely on Facebook user id as a permanent user identifier
  • Zooming an element and its contents— an alternative to CSS3's zoom property?
  • ProgressBar Paint Method?
  • C: Custom strlen() library function
  • addressing in assembler
  • CS1703: In Xamarin.Droid, should I use the .Net Standard windowsruntime.dll located in Mono.Framewor
  • Neo4j: Filter nodes based on aggregate function
  • What is corresponding c++ data type to SQL numeric(18,0) data type?
  • Owin Authentication and claims in asp.net how to access user data
  • NUnit 3.0 TestCase const custom object arguments
  • msbuild create itemgroup from property group
  • Updating both a ConcurrentHashMap and an AtomicInteger safely
  • WPF Visiblity Binding to Boolean Expression with multiple Variables
  • Conversion from string “a” to type 'Boolean' is not valid
  • Caching attributes in superclass
  • Replace value with Factor in r data.table
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • Getting last autonumber in access
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • Arrays break string types in Julia