# Database Discussions > Microsoft SQL Server 2005 >  IDENTITY_INSERT in MSSQL 2005

## dbnewbie

Hi ,

I use MSsql server 2005 with the compatability mode set to 2000.One of the tables, has an IDENTITY Column.I need to restore a earlier backed copy of the table.So, i did the following :

set IDENTITY_INSERT MYTABLE ON

insert into MYTABLE(ID,NAME) values(23,'XYZ')

However, i get the following error :

[Error Code: 544, SQL State: S0001] Cannot insert explicit value for identity column in table 'MYTABLE' when IDENTITY_INSERT is set to OFF.

Can anyone tell me why the set IDENTITY_INSERT does not work ? I need to disable the IDENTITY, do the restore and then enable the IDENTITY again.Also, i need to be able to do this only thorugh SQL issued via JDBC.Please help.

Thanks,

----------


## rmiao

It works in ssms query window. Did you run those in same session?

----------


## karginartur

place GO command.

SET IDENTITY_INSERT MYTABLE ON
GO

INSERT INTO .....

SET IDENTITY_INSERT MYTABLE OFF
GO

----------


## dbnewbie

Hai rmiao /karginartur,

            Thanks for your reply.I was issuing the queries through a tool(which connects to the DB using JDBC). I believe each of the queries were sent in different sessions.Hence it did not work.I tried the following JDBC code and it worked for me !

Class.forName("com.microsoft.sqlserver.jdbc.SQLSer  verDriver");
                        Connection con=DriverManager.getConnection("jdbc:sqlserver://10.102.31.71:1433","<USERNAME>","<PASSWORD");
                        Statement stmt = con.createStatement();
                        stmt.executeUpdate("set IDENTITY_INSERT MYTABLE ON");
                        stmt.executeUpdate("insert into mytable(id,name) values(7,'test')");

    Also, issuing the query using MS SQl SERVER Management Studio  also worked !!

Thanks,
 :Smilie:

----------

