Adding string to the primary key?

Question

I want to add some string with the primary key value while creating the table in sql?

Example:

my primary key column should automatically generate values like below:

'EMP101'
'EMP102'
'EMP103'

How to achieve it?

Answer 1

Try this: (For SQL Server 2012)

UPDATE MyTable
SET EMPID = CONCAT('EMP' , EMPID)

Or this: (For SQL Server < 2012)

UPDATE MyTable
SET EMPID = 'EMP' + EMPID

• SQLFiddle for SQL Server 2008
• SQLFiddle for SQL Server 2012

Answer 2

Since you want to set auto increment in VARCHAR type column you can try this table schema:

CREATE TABLE MyTable
    (EMP INT NOT NULL IDENTITY(1000, 1)
    ,[EMPID] AS 'EMP' + CAST(EMP AS VARCHAR(10)) PERSISTED PRIMARY KEY
    ,EMPName VARCHAR(20))
;

INSERT INTO MyTable(EMPName) VALUES
('AA')
,('BB')
,('CC')
,('DD')
,('EE')
,('FF')

Output:

|  EMP |   EMPID | EMPNAME |
----------------------------
| 1000 | EMP1000 |      AA |
| 1001 | EMP1001 |      BB |
| 1002 | EMP1002 |      CC |
| 1003 | EMP1003 |      DD |
| 1004 | EMP1004 |      EE |
| 1005 | EMP1005 |      FF |

See this SQLFiddle

Here you can see EMPID is auto incremented column with Primary key.

Source: HOW TO SET IDENTITY KEY/AUTO INCREMENT ON VARCHAR COLUMN IN SQL SERVER (Thanks to @bvr)

Answer 3

What the rule of thumb is, is that never use meaningful information in primary keys (like Employee Number / Social Security number). Let that just be a plain autoincremented integer. However constant the data seems - it may change at one point (new legislation comes and all SSNs are recalculated).

Answer 4

it seems the only reason you are want to use a non-integer keys is that the key is generated as string concatenation with another column to make it unique.

From a best practice perspective, it is strongly recommended that integer primary keys are used, but often, this guidance is ignored.

May be going through the following posts might be of help:

Should I design a table with a primary key of varchar or int?

SQL primary key: integer vs varchar

Answer 5

You can achieve it at least in two ways:

1. Generate new id on the fly when you insert a new record
2. Create INSTEAD OF INSERT trigger that will do that for you

If you have a table schema like this

CREATE TABLE Table1
([emp_id] varchar(12) primary key, [name] varchar(64))

For the first scenario you can use a query

INSERT INTO Table1 (emp_id, name)
SELECT newid, 'Jhon'
  FROM
(
  SELECT 'EMP' + CONVERT(VARCHAR(9), COALESCE(REPLACE(MAX(emp_id), 'EMP', ''), 0) + 1) newid
    FROM Table1 WITH (TABLOCKX, HOLDLOCK)
) q

Here is SQLFiddle demo

For the second scenario you can a trigger like this

CREATE TRIGGER tg_table1_insert ON Table1
INSTEAD OF INSERT AS
BEGIN
  DECLARE @max INT
  SET @max = 
  (SELECT COALESCE(REPLACE(MAX(emp_id), 'EMP', ''), 0)
     FROM Table1 WITH (TABLOCKX, HOLDLOCK)
  )

  INSERT INTO Table1 (emp_id, name)
  SELECT 'EMP' +  CONVERT(VARCHAR(9), @max + ROW_NUMBER() OVER (ORDER BY (SELECT 1))), name
    FROM INSERTED
END

Here is SQLFiddle demo

Answer 6

I am looking to do something similar but don't see an answer to my problem here.

I want a primary Key like "JonesB_01" as this is how we want our job number represented in our production system.

--ID | First_Name | Second_Name | Phone | Etc.. -- Bob Jones 9999-999-999

--ID = "Second_Name"+"F"irst Initial+"_(01-99)"

The number 01-99 has been included to allow for multiple instances of a customer with the same surname and first initial. In our industry it's not unusual for the same customer to have work done on multiple occasions but are not repeat business on an ongoing basis. I expect this convention to last a very long time. If we ever exceed it, then I can simply add a third interger.

I want this to auto populate to keep data entry as simple as possible.

I managed to get a solution to work using Excel formulars and a few helper cells but am new to SQL.

--CellA2 = JonesB_01 (=concatenate(D2+E2))

--CellB2 = "Bob"

--CellC2 = "Jones"

--CellD2 = "JonesB" (=if(B2="","",Concatenate(C2,Left(B2)))

--CellE2 = "_01" (=concatenate("_",Text(F2,"00"))

--CellF2 = "1" (=If(D2="","",Countif($D$2:$D2,D2))

Thanks.

Answer 7

SELECT 'EMP' || TO_CHAR(NVL(MAX(TO_NUMBER(SUBSTR(A.EMP_NO, 4,3))), '000')+1) AS NEW_EMP_NO 
  FROM 
     (SELECT 'EMP101' EMP_NO 
        FROM DUAL
       UNION ALL
      SELECT 'EMP102' EMP_NO 
        FROM DUAL
       UNION ALL
      SELECT 'EMP103' EMP_NO 
        FROM DUAL 
 ) A