Know identity before insert

Question

I want to copy rows from the table within the table itself. But before inserting I need to modify a varchar column appending the value of identity column to it. My table structure is:

secID docID secName secType secBor
 1     5     sec-1   G        9
 2     5     sec-2   H        12
 3     5     sec-3   G        12
 4     7     sec-4   G        12
 5     7     sec-5   H        9

If I want to copy data of say docID 5, currently this runs through a loop one row at a time.

I can write my query as

insert into tableA (docID, secName, secType, secBor)
select 8, secName, secType, secBor from tableA where docID = 5

But how can I set value of secName before hand so that it becomes sec-<value of secID column>?

The onnly way would be to not have secID as an Identity column and use `ROW_NUMBER()` to set these values on insert — Matt, Sep 19 '18 at 18:08
Why not just do `select 8, 'sec-' + CAST([secID] AS varchar(20)), secType, secBor from tableA where docID = 5` — Ryan Wilson, Sep 19 '18 at 18:08
@RyanWilson, but this will give me secID of previous record. I will need it for the current record. So kind of say, need to know in advance the identity key value getting inserted. — CFML_Developer, Sep 19 '18 at 18:14
@SalmanA, yes looks like trigger is the only solution. Because changing table structure is not an option at all because that will involve other unwarranted code changes at other places. — CFML_Developer, Sep 19 '18 at 18:47
Since the actual **value** of the identity isn't determined **until the row is actually, physically inserted**, there is **NO** reliable and precise way to know an identity value *before* the insert. Period. Don't waste time on this - you just **CANNOT** know before the insert what that value is going to be. — marc_s, Sep 19 '18 at 19:02

Salman A · Answer 1 · 2018-09-20T22:10:39.110

2

Don't try to guess the value of identity column. In your case you could simply create a computed column secName AS CONCAT('sec-', secID). There is no further need to update that column.

DB Fiddle

It is also possible to create an AFTER INSERT trigger to update the column.

edited Sep 20 '18 at 22:10

answered Sep 19 '18 at 18:32

Salman A

262,204
82
430
521

+1 from me sir. So, I take it that when rolling back a transaction it doesn't reseed the identity column?? That's interesting behavior. – Ryan Wilson Sep 19 '18 at 18:44
Yup. Sensible too. – Salman A Sep 19 '18 at 18:45
Why is it sensible? I mean, what harm could befall a table if you cancel all the insertions (ROLLBACK) and reset the identity column to what it was before the ROLLBACK? – Ryan Wilson Sep 19 '18 at 18:45
Same way it makes sense not to reseed if a row was deleted. – Salman A Sep 19 '18 at 18:46
I see why if you delete a row that already existed, but in the case of a ROLLBACK, it's as if the insert never took place... – Ryan Wilson Sep 19 '18 at 18:47
2

If anyone is curious like I was about this behavior of the Identity column, this post explains why it doesn't reseed it pretty well (https://stackoverflow.com/questions/282451/sql-identity-autonumber-is-incremented-even-with-a-transaction-rollback) – Ryan Wilson Sep 19 '18 at 18:53

score 0 · Answer 2 · edited Sep 19 '18 at 18:33

0

Adding to my comment something like:

insert into tableA (docID, secName, secType, secBor)
select 
  ROW_NUMBER() OVER (ORDER BY DocID), 
  'Sec -'+ ROW_NUMBER() OVER (ORDER BY DocID),
  secType, secBor 
from tableA 
where docID = 5

edited Sep 19 '18 at 18:33

Kamil Gosciminski

16,547
8
49
72

answered Sep 19 '18 at 18:09

Matt

825
2
13
25

How would this work? He wants the Identity column value appended to sec-, not a ROW_NUMBER() value, If the 6th record in the table had a DocumentId of 5, then ROW_NUMBER() Would return 4 but the identity would be 6. OP's post "I need to modify a varchar column appending the current identity key value in it." – Ryan Wilson Sep 19 '18 at 18:11
`ROW_NUMBER()` would return a sequential number, the ordering is arbitrary in this example. The `IDENTITY` column is the same function just persisted, so if he wants to get the same value at execution time for the `SecId` and `SecName` the only way other than some convoluted trigger would be to generate and insert - see https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017 – Matt Sep 20 '18 at 13:35

score 0 · Answer 3 · edited Sep 19 '18 at 19:01

In SQL Server 2012 and later, you can achieve this by using the new sequence object.

CREATE SEQUENCE TableAIdentitySeqeunce
START WITH 1  
INCREMENT BY 1 ;  

GO

create table TableA 
(
    secId int default (NEXT VALUE FOR TableAIdentitySeqeunce) not null primary key,
    varcharCol nvarchar(50)
)  

declare @nextId int;
select @nextId = NEXT VALUE FOR TableAIdentitySeqeunce

insert TableA (secId, varcharCol) 
values (@nextId, N'Data #' + cast(@nextId as nvarchar(50)))

score 0 · Answer 4 · answered Sep 19 '18 at 18:24

0

Since SQL Server does not have GENERATED ALWAYS AS ('Sec - ' + id) the only simple option I see is to use a trigger.

answered Sep 19 '18 at 18:24

The Impaler

45,731
9
39
76

Know identity before insert

4 Answers4