CODEWITHSUNDEEP
sql-serversql-server-2008
Mark Hizzle
Mark Hizzle

Reputation: 55

How can I prefix an auto incremented column with a number

I have an existing table that has a primary key column using the identity increment feature. Is there any way to update that column somehow so that when a new record is created the auto incremented column can be prefixed with another number? For example, if the next record created generates a primary key of 450016, I want it to have a prefix of 13 so the final result that gets saved in the db is 13450016.

Upvotes: 1

Views: 2262

Answers (3)

Mark Hizzle
Mark Hizzle

Reputation: 55

I can't just reseed it because it needs to always be prefixed with the same number. What I'm going to just do is a mixture of some suggestions offered. What I'm going to do is rename my primary key column, create a new column and name it to what my previous primary key column's name was, and then add a trigger that will update my new column's value to whatever the identity value is with a specified number prefixed. One of my main issues I was having is I have a lot of code referencing my primary key column name so by doing some renaming that issue should be resolved. Thanks everyone!

Upvotes: 0

russ
russ

Reputation: 624

There are maybe 4 ways it might be:

(1) Other tables do not reference this primary key and you have not deleted a row (numbers are sequential). (2) Other tables do not reference this primary key but numbers are not sequential (rows have been deleted). (3) Numbers are sequential but other tables reference this primary key. (4) There is both referencing and deleted rows.

In any of these ways the column can be replaced.

Try to run these chex on the table:

EXEC sp_fkeys 'tablename'

Use the code as it is but replace tablename with the name of the table (make sure you're in the right database and keep the single quote marks).

If there are any rows shown by this stored procedure, we'll need to temporarily drop the foreign key constraint(s); refer to column FK_Name and list down any values in this column. Let me know yes if it produces any rows.

Then run the following code:

SELECT TOP 1 NAME_OF_ID_COLUMN - ROW_NUMBER()OVER(ORDER BY ID) AS NSEQ
FROM TABLE_NAME ORDER BY NSEQ DESC

Use the code as it is but replace NAME_OF_ID_COLUMN with the actual column name that we are working on and replace TABLE_NAME with the name of the table that column is in.

If the NSEQ column produces a value of 0 then you haven't deleted, if 1 then you have.

If you wish, let me know the answers in a comment and I'll sort the relevant code.

Upvotes: 0

Karl Kieninger
Karl Kieninger

Reputation: 9129

"I guess I was hoping to find a way to actually manipulate the identity column instead of having to create a new computed column."

Identity columns are pretty stubborn. You best bet if you want to do something like this is, I think to copy everything to a new table. If you wanted to prefix with 13 what you are really doing is adding 13000000 to everythign that exist and counting up from there.

CREATE TABLE tbl (ID INT IDENTITY,Val INT)
INSERT INTO tbl VALUES (1),(1)

SELECT * FROM tbl

CREATE TABLE tbl2 (ID INT IDENTITY,Val INT)

SET IDENTITY_INSERT tbl2 ON

INSERT INTO tbl2 (ID,val)
      SELECT ID+1300000, Val
        FROM tbl WITH (HOLDLOCK TABLOCKX)

SET IDENTITY_INSERT tbl2 OFF

DROP TABLE tbl

EXECUTE sp_rename N'tbl2', N'tbl', 'OBJECT'  

SELECT * FROM tbl

INSERT INTO tbl VALUES (1),(1)
SELECT * FROM tbl

DROP TABLE tbl

If, instead you want to just reseed the identity to make leave everything existing as is and have the next record star at 13xxxxxx then instead you can reseed:

CREATE TABLE tbl (ID INT IDENTITY,Val INT)
INSERT INTO tbl VALUES (1),(1)

SELECT * FROM tbl

DBCC CHECKIDENT ('tbl', RESEED, 1300002);

INSERT INTO tbl VALUES (1),(1)
SELECT * FROM tbl

DROP TABLE tbl

If you can do this multiple times but you can never go backwards so your prefixes must always increase the seed value.

Upvotes: 1

Related Questions