CODEWITHSUNDEEP
sql-server
asd
asd

Reputation: 1117

Inserting a row in identity column of table variable

I have the following table variable:

declare @TestTable2 as table (A int identity)

I'm trying to insert some value (for instance a 1) without altering the line of code above.

I've been trying to do the following

declare @TestTable2 as table (A int identity)



SET IDENTITY_INSERT @TestTable2 ON
INSERT @TestTable2 (A)
VALUES (1)

But I get an error (Incorret syntax near '@TableTEST2')

How can I insert a value in the column A?

Upvotes: 3

Views: 12666

Answers (3)

Jeff Pearce
Jeff Pearce

Reputation: 581

I was able to accomplish the task by setting the statements as variables and then executing them with a concatenated statement.

BEGIN TRANSACTION;

    -- =============================================
    -- SET IDENTITY INSERT ON AND INSERT THE NEW RECORD.
    -- =============================================
    SET @STATEMENT1 = 'SET IDENTITY_INSERT ' + @TestTable2 + ' ON;';
    SET @STATEMENT2 = 'INSERT INTO ' + QUOTENAME(@TestTable2) + ' (A) VALUES (1);';
    SET @STATEMENT3 = 'SET IDENTITY_INSERT ' + @TestTable2 + ' OFF;';

    EXEC (@STATEMENT1 + @STATEMENT2 + @STATEMENT3);

COMMIT TRANSACTION;

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146603

Check [MSDNlink][1] below It specifies the Restriction.

EDIT. After it became clear that you do not want to insert a specific value (that's what Identity_Insert is for) all you want is to insert a new auto-generated value for columne A, then what you need to do is 

 Insert @TestTable2 DEFAULT VALUES

https://connect.microsoft.com/SQLServer/feedback/details/757012/set-identity-insert-on-table-variables

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

No this is not possible, SET IDENTITY_INSERT is not apply on Table variables

If want use SET IDENTITY_INSERT try to create temp table and use SET IDENTITY_INSERT on it

CREATE TABLE #TestTable2(A int identity)
SET IDENTITY_INSERT #TestTable2 ON

INSERT INTO #TestTable2 (A) VALUES (1)

For more info click HERE

Upvotes: 1

Related Questions