SET IDENTITY_INSERT CI_Equipment ON ? sql server 2008

Hello,
i am trying to execute the command
SET IDENTITY_INSERT CI_Equipment ON
On a table named CI_Equipment, it works well...Command(s) completed successfully.

The problem is when i try to insert a new row

INSERT INTO [CI_TevaAbic].[dbo].[CI_Equipment]
           ([EquipmentId]
           ,[EquipmentName]
           ,[EquipmentTypeId]
           ,[RegionId]
           ,[TimeCreated])
     VALUES
           (1
           ,'name'
           ,1
           ,1
           ,'01-01-1900'
           )
GO


it still shows me
Cannot insert explicit value for identity column in table 'CI_Equipment' when IDENTITY_INSERT is set to OFF.

What can I do?
contelAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to run the insert on the same database, and on the same session

USE [CI_TevaAbic]
GO
SET IDENTITY_INSERT [dbo].CI_Equipment ON
INSERT INTO [dbo].[CI_Equipment]
           ([EquipmentId] 
           ,[EquipmentName]
           ,[EquipmentTypeId]
           ,[RegionId]
           ,[TimeCreated])
     VALUES
           (1
           ,'name'
           ,1
           ,1
           ,'01-01-1900'
           )
SET IDENTITY_INSERT [dbo].CI_Equipment OFF
GO

Open in new window


0
 
Pratima PharandeCommented:
If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

try with unused value for id
0
 
waltersnowslinarnoldCommented:
Use the following sample, it'll work.
USE [CI_TevaAbic]
GO
SET IDENTITY_INSERT [CI_TevaAbic].[dbo].CI_Equipment ON
INSERT INTO [CI_TevaAbic].[dbo].[CI_Equipment]
           ([EquipmentId] 
           ,[EquipmentName]
           ,[EquipmentTypeId]
           ,[RegionId]
           ,[TimeCreated])
     VALUES
           (1,'name',1,1,'01-01-1900')
SET IDENTITY_INSERT [CI_TevaAbic].[dbo].CI_Equipment OFF
GO

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
contelAuthor Commented:
Thank you very much!!
0
 
Alpesh PatelAssistant ConsultantCommented:
Please set identity on
0
 
Anthony PerkinsCommented:
waltersnowslinarnold,

Did you just copy and paste the solution posted by angelIII here http:#34951007?
0
 
DonaldMarkSandersCommented:
Guys I was banging my head against the wall trying to get this to work and nothing worked at all.  I did everything you guys talked about above and it DID NOT WORK AT ALL.  Until I did the following:

For MS SQL 2008 and using Microsoft SQL Management Studio I had to click Tool - Options -  Designers and then UNCHECK "Prevent saving changes that require table recreation".  I saved the option and then went to my table changed the field to a non-identity field and then added the records I needed and then changed it back when I was done.

IT WORKED!
0
 
Anthony PerkinsCommented:
>>Guys I was banging my head against the wall trying to get this to work and nothing worked at all.  <<
The author is not using the Design mode to change the IDENTITY attribute.  All they want to do is explicitly INSERT the value into a column with an IDENTITY column, which is perfectly doable using the code angelIII posted provided:
1. They have the appropriate permissions.
2. They do not violate any constraint.

So your point is somewhat irrelevent as the "Prevent saving changes that require table recreation" checkbox only applies to changing the table schema using "Table and Database Designers" as shown in the same tab under Options.
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.