sql copy of identity column as a string

Question

Im creating a procedure that simply creates a copy of a row but changes some values.

In the newly created record (which is a copy of an existing record) I need one of the fields to be a string representation of the recordID (an auto-generated IDENTITY column) of the newly created record.

CREATE PROC sp @RecordID int
AS
BEGIN
    INSERT INTO TheTable([RecordName], ...)
    SELECT CAST(@RecordID as nvarchar(500)), ...
    FROM TheTable
    WHERE [RecordID] = @RecordID
END

my code is wrong because it sets the RecordName to the RecordID of the record being copied FROM. I need the RecordName to be the same as the RecordID of the record being created.

Thanks for the help

So you want a column with a character representation of its own new identity value? — Alex K., Sep 12 '12 at 14:49
So you want your table to look like: `(RecordId, RecordName): (1,1) (2,2) (3,3) (4,4), ...`? Why do you want to do that? — Mahmoud Gamal, Sep 12 '12 at 14:50
No idea why but this is what was requested. I thought to copy the ID of the record being copied from but was asked to correct it — parliament, Sep 12 '12 at 14:51
A Trigger? see http://stackoverflow.com/questions/8171622/get-identity-value-in-the-same-t-sql-statement-it-is-created-in — Alex K., Sep 12 '12 at 14:53
Thanks alex. That trigger is certainly a solution if it has to be done 'in one statement' but being that my logic is wrapped inside the SP, simply updating is will work for me. Thanks! — parliament, Sep 12 '12 at 15:05

score 2 · Answer 1 · answered Sep 12 '12 at 14:51

I don't think that what you describe is possible — the identity value isn't available until the statement completes — but there's a simple workaround:

CREATE PROC sp @RecordID int
AS
BEGIN
    INSERT INTO TheTable([RecordName], ...)
    SELECT 'temporary dummy value', ...
    FROM TheTable
    WHERE [RecordID] = @RecordID
    ;

    UPDATE TheTable
    SET [RecordName] = CAST(@@IDENTITY as nvarchar(500))
    WHERE [RecordID] = @@IDENTITY
    ;
END

(See the documentation for @@IDENTITY.)

score 1 · Answer 2 · answered Sep 12 '12 at 14:52

Sounds like you'll need to do this in 2 steps - insert the new data into the table, then update the record with the new recordID

For example (assuming Sql Server),

declare @myNewID int    

INSERT INTO TheTable([RecordName], ...)
SELECT CAST(@RecordID as nvarchar(500)), ...
FROM TheTable
WHERE [RecordID] = @RecordID

set @myNewID = SCOPE_IDENTITY()

UPDATE TheTable
    set [RecordName] = CAST(@myNewID as nvarchar(500))
WHERE [RecordID] = @myNewID

sql copy of identity column as a string

2 Answers2