Inserting a column in a table that creates an ID and row number

Posted on 2016-10-19
Medium Priority
Last Modified: 2016-10-20
I have a trigger on a table (like an audit trigger) that updates a backup table whenever the main table is updated. If someone updates the main table "Client", all the changes are captured along with the old records in the "ClientBackup" table.
I created the backup table using INSERT INTO

What I want to do is add a column to this backup table for identification purposes, possibly to create a unique key (but not certain I need that yet), but I do want to add the column with row numbers to the backup table and auto increment that when new data is added.

Because this is updated via an UPDATE Trigger the column would need to auto update whenever the trigger fires.

I know I can add a column to an existing table in order to number the rows with


The current table has 471 rows if I run the above code and add the IDENTITY column I'll have rows 1 through 471. How would I create a column that auto updates the next time the trigger fires? I'm learning SQL but in other code I would create a variable that holds the value of IDENTITY and then add one to the current value, but can I do that inside a trigger and in SQL?

Here is the trigger code
---- CODE----

ON [cd].[client471]
INSERT INTO cd.client471bckup
    ([uniqueid_c], [clientid_c],
    [txstatus_c], [compemploy01_c], [compemploy02_c], [compemploy03_c],
      [compemploy04_c], [compemploy05_c], [compemploy06_c], [compemploy07_c], [compemploy08_c],
    [compemploy09_c], [compemploy10_c], [compemploy11_c], [compemploy12_c],
    [livingarr_c], [livingarrchange_d], [livingarrlast_c], [lastsubstat_d], [createuser_c], [create_dt]
    i.uniqueid_c, i.clientid_c, i.txstatus_c,i.compemploy01_c,i.compemploy02_c,i.compemploy03_c,i.compemploy04_c,
    i.compemploy09_c,i.compemploy10_c, i.compemploy11_c, i.compemploy12_c,
    i.livingarr_c,i.livingarrchange_d,i.livingarrlast_c,i.lastsubstat_d,i.createuser_c, i.create_dt
FROM cd.client471 c
Inner join inserted i on c.clientid_c = i.clientid_c

Question by:John Mahoney
  • 2
LVL 41

Accepted Solution

Kyle Abrahams earned 1000 total points
ID: 41850865
You don't need to worry about that column.  EG: you add the identity column and then you do your insert as normal (ignoring the identity column).  When you do the next select it will auto increment for you.
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 total points
ID: 41852478
For a column with the IDENTITY property, SQL Server automatically generates the value for the column during an INSERT.  Therefore, you can't provide the value yourself (exception: you can explicitly "tell" SQL that you will be providing the identity values to be INSERTed).

For example:
CREATE TABLE table1 (column1 int IDENTITY(1, 1) NOT NULL, column2 varchar(30))
INSERT INTO table1 VALUES('column2_value')
Note that you basically ignore the identity column on a normal INSERT statement.

Author Comment

by:John Mahoney
ID: 41852484
Thank you.

Author Closing Comment

by:John Mahoney
ID: 41852488
Again thanks

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question