CODEWITHSUNDEEP
sqlsql-server
CFML_Developer
CFML_Developer

Reputation: 1605

Know identity before insert

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>?

Upvotes: 1

Views: 475

Answers (4)

Salman Arshad
Salman Arshad

Reputation: 272296

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.

Upvotes: 2

Lorentz Vedeler
Lorentz Vedeler

Reputation: 5311

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)))

Upvotes: 0

Matt
Matt

Reputation: 835

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

Upvotes: 0

The Impaler
The Impaler

Reputation: 48850

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

Upvotes: 0

Related Questions