INSERT INTO while IDENTITY_INSERT is set to OFF

I have the following - however when i try to run it i get "Cannot insert explicit value for identity column in table 'DAAutoReportsLimit' when IDENTITY_INSERT is set to OFF."
INSERT INTO DAAutoReportsLimit(ID, ID_AutoReport, Limit)
SELECT
  350163, ID_AutoReport, Limit
FROM
  DAAutoReportsLimit
WHERE
  ID = 349730

Open in new window

can i simply set IDENTITY_INSERT is set to ON- while my database is in use then turn it of once i have added my rows?
LVL 1
csePixelatedAsked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
You need to explicitly turn it on:

SET IDENTITY_INSERT DAAutoReportsLimit ON;

INSERT INTO DAAutoReportsLimit ( ID ,
                                 ID_AutoReport ,
                                 Limit )
            SELECT 350163 ,
                   ID_AutoReport ,
                   Limit
            FROM   DAAutoReportsLimit
            WHERE  ID = 349730;

SET IDENTITY_INSERT DAAutoReportsLimit OFF;

Open in new window


Caveat: Never forget to turn it off after your job is done.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here