SET IDENTITY_INSERT Inside my parameter it does not work

Question

I am trying to insert values into a table with an identity, I know I have to use before insert: SET IDENTITY_INSERT.

What I executed:

DECLARE @TABLE_NAME NVARCHAR(200), @QUERY NVARCHAR(500) SET @TABLE_NAME = 'devListaTrabajo' SET @QUERY = 'SET IDENTITY_INSERT ' + @TABLE_NAME + ' ON' PRINT @QUERY EXEC (@QUERY) INSERT INTO devListaTrabajo (lisId,lisDescripcion,lisEstado,topId,parCLS,parcod) VALUES (1,'H',1,1,'H','LH')

And my result:

SET IDENTITY_INSERT devListaTrabajo ON Msg 544, Level 16, State 1, Line 79 Cannot insert explicit value for identity column in table 'devListaTrabajo' when IDENTITY_INSERT is set to OFF.

Result Image

If you ask me why I don't use the normal statement without the query inside parameter, it is because I plan to do a cycle where the table is changed, and I intend to use the SET IDENTITY_INSERT.

Maybe I'm skipping some SQL rule for the use of this statement. Please, I ask for an explanation or another alternative to solve it.

Joel Coehoorn · Accepted Answer · 2020-06-11 02:33:30Z

The contents of the EXEC() command run in a separate context from the rest of the code. That context runs, completes, and you return to original context where identity insert was never changed.

If you want to make this happen dynamically like that, you must put the entire statement into the @QUERY variable.

Excellent, I already included the insert inside the query and it ran perfectly, thank you!

Collectives™ on Stack Overflow

SET IDENTITY_INSERT Inside my parameter it does not work

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related