Insert statement Issue

[firoz.raj]

HI Friends , i have been trying to insert the record but it says the following .let me know some comment .
Cannot insert explicit value for identity column in table 'PS_SalesDetail' when IDENTITY_INSERT is set to OFF.

insert into ps_salesdetail(SaleInternalDate,SaleSerial,Serial,ItemID,SalesmenID,Serial1,Serial2,Serial3) 
       values('2012-07-17 00:00:00.000','CLEHDTST015-00000011', 383137, 'CLE-29MW020','', 'GRY_GRAY', 'L', 'GRY_GRAY/L' )

The following statement is also not working .

set IDENTITY_INSERT on ps_salesdetail

[TysonLPrice]

An identity column increments itself and it would defeat the purpose of having it setting it off and on. Take it out of the insert and let it do its own thing. If you really need a key so that you can set values than use a key not identity.

[FunkyDexter]

The error is telling you that one of the columns on that table is an identity column and you're trying to insert a value into it. This is not normally allowed as identity columns are intended create their own values.

set IDENTITY_INSERT on ps_salesdetail

The on should go at the end.

I would question whether you want to set identity insert on though. Why are you not just letting the idenity column generate it's own value?

[firoz.raj]

i did off the identity col .and it is working . why ?

set identity_insert tableName off

[TysonLPrice]

i did off the identity col .and it is working . why ?

set identity_insert tableName off

The real question should be why are you circumventing an identity column? Unless you designed the table or know how it fits into the system you may be doing a bad thing working around it like you seem to be.

[firoz.raj]

even identity_insert is on it says the following . let me know please .
Cannot insert explicit value for identity column in table 'PS_SalesDetail' when IDENTITY_INSERT is set to OFF.
{CALL sp_MSins_Ps_salesdetail200 (2010-01-09 00:00:00.000, 'TATONZST035-00001983 ', 115240, 'TAT8S112C1000030 ', 'AC_ASSORTED COM', 'OZ ', '_ ', '_ ', 'AC_ASSORTED COM/OZ ', 0.000000000, 2.000000000, 25.000000000, 0, 0.000000000, 0.000000000, 0.000000000, 0.000000000, NULL, 1, 0, 0, NULL, NULL, 0, 0, 0, NULL, 0.000000000, '8S112C1000030 ', 1, NULL, 0, 0, '100000000356
Transaction sequence number and command ID of last execution batch are 0x0000045000025DAD001600000000 and 1.

alter procedure "sp_MSins_Ps_salesdetail200" @c1 datetime,@c2 char(30),@c3 int,@c4 char(40),@c5 char(15),@c6 char(15),@c7 char(15),@c8 char(15),@c9 char(65),@c10 numeric(20,9),@c11 numeric(20,9),@c12 numeric(20,9),@c13 bit,@c14 numeric(20,9),@c15 numeric(20,9),@c16 numeric(20,9),@c17 numeric(20,9),@c18 varchar(50),@c19 bit,@c20 tinyint,@c21 bit,@c22 datetime,@c23 char(30),@c24 bit,@c25 bit,@c26 bit,@c27 char(15),@c28 numeric(20,9),@c29 char(40),@c30 tinyint,@c31 char(15),@c32 int,@c33 bit,@c34 varchar(40),@c35 nvarchar(50),@c36 int,@c37 int,@c38 tinyint,@c39 datetime,@c40 char(5),@c41 char(15)  
  
AS  
BEGIN  
  
Declare @Error tinyint  
exec PS_SP_ValidateSalesModification @c2, @Error output  
  
If @Error <> 0  
  Return  
begin 
if not exists(select * from "Ps_salesdetail" where "SaleSerial"=@c2 and  "Serial"=@c3)
set identity_insert Ps_salesdetail on  
	insert into "Ps_salesdetail"(   
	"SaleInternalDate", "SaleSerial", "Serial", "ItemID", "Serial1", "Serial2", "Serial3", "Serial4", "SerialCombination", "QtyReturned", "Qty", "Price", "PriceChanged", "Discount", "ManualDiscount", "ManualDiscountPct", "Taxes", "Description", "Error", "ReturnFlag", "Coupon", "ReturnSaleDate", "ReturnSaleNumber", "Posted", "PromotionFlag", "CouponFlag", "PromotionID", "PromotionDiscount", "ItemName", "MatrixFlag", "PromotionCode", "PromotionGroup", "ReprintInReceiptFooter", "BarCode", "Comment", "ReturnSaleDSerial", "groupid", "ReturnApplied", "ReturnSalesDate", "TaxCodeID", "WarehouseID"  
	 )  
	  
	values (   
	@c1, @c2, @c3, @c4, @c5, @c6, @c7, @c8, @c9, @c10, @c11, @c12, @c13, @c14, @c15, @c16, @c17, @c18, @c19, @c20, @c21, @c22, @c23, @c24, @c25, @c26, @c27, @c28, @c29, @c30, @c31, @c32, @c33, @c34, @c35, @c36, @c37, @c38, @c39, @c40, @c41  
	 )  
  
set identity_insert Ps_salesdetail off  
End
END  
  
  
GO

[TysonLPrice]

I can't think but you are on the wrong track forcing identity inserts. If this is your own system then have at it. If you are working in a commercial environment than I think you should speak to a co-worker about what you are trying to do.

Here is a set up you can "play" with

-- create table with identity column
CREATE TABLE #VBForums( Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 Something VARCHAR(10) NOT NULL )

-- don't allow inserts
set IDENTITY_INSERT #VBForums off

 Insert into #VBForums(id,Something) values(1,'SomeThing')
 
-- Msg 544, Level 16, State 1, Line 1
-- Cannot insert explicit value for identity column in table '#VBForums

-- Allow inserts
 set IDENTITY_INSERT #VBForums on

 Insert into #VBForums(id,Something) values(1,'SomeThing')
 
 -- (1 row(s) affected)
 
 -- shut it back off
 set IDENTITY_INSERT #VBForums off
 
 Insert into #VBForums(id,Something) values(2,'SomeThing')
 
 -- Msg 544, Level 16, State 1, Line 1
-- Cannot insert explicit value for identity column in table '#VBForums
 
 drop table #VBForums

[TysonLPrice]

I apoligize if you already know this and I seem to be “beating a dead horse”. I’m just concerned you may not understand the function of an identity column. I’m trying to demonstrate that here. The code below can be plugged in and run ASIS.

 -- create table with identity column
CREATE TABLE #VBForums( Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 Something VARCHAR(15) NOT NULL )
 
 -- don't allow identity inserts
set IDENTITY_INSERT #VBForums off

 Insert into #VBForums(Something) values('SomeThing - 1')
 Insert into #VBForums(Something) values('SomeThing - 2')
 Insert into #VBForums(Something) values('SomeThing - 3')
 
 -- Notice the ID is incremented automatically
 select * from #VBForums
 
 /*
 
ID  SomeThing  
1	SomeThing - 1
2	SomeThing - 2
3	SomeThing - 3
*/

-- In many applications the ID value generated is used as a foriegn key to other tables
-- The next insert would create normally  an ID value of "4"
-- You seem to what to circumvent that

-- Allow identity inserts
 set IDENTITY_INSERT #VBForums on

 Insert into #VBForums(id,Something) values(7,'SomeThing - 4')
 
  -- That "broke" the numbering that would have occured.  Instead of "4"it is "7"
 select * from #VBForums
 
 /*
 
ID  SomeThinf
1	SomeThing - 1
2	SomeThing - 2
3	SomeThing - 3
7	SomeThing - 4
*/
 
 drop table #VBForums

I may be all wet as far as your application goes but I've worked in a number of commercial IT shops where we rely on identity columns to build our foriegn keys. You have several entries in this post trying to get around it and it makes me nervous :-)

I'm thinking it is there for a reason. I'll get off my soapbox now :-)