Re: Insert statement Issue
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.
Re: Insert statement Issue
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.
Code:
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?
Re: Insert statement Issue
i did off the identity col .and it is working . why ?
Code:
set identity_insert tableName off
Re: Insert statement Issue
Quote:
Originally Posted by
firoz.raj
i did off the identity col .and it is working . why ?
Code:
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.
Re: Insert statement Issue
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.
Code:
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
Re: Insert statement Issue
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
Code:
-- 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
Re: Insert statement Issue
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.
Code:
-- 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 :-)
