locked
Scope Identity is not generating RRS feed

  • Question

  • User1052662409 posted

    Hi All,

    Below is my store procedure. At very first I am inserting a row in  a table and just after after insertion I am trying to get the auto incremented id of table.

    The problem is that my first code block is working fine and inserting the row in table but the second block not inserting the data in table. I guess , that I am not getting the Scope_Identity()

    -- row insertion completed and id is there in table
                            DECLARE @id INT;
    			SET @id = SCOPE_IDENTITY()
    			
    			INSERT INTO Leave_Type_Master (vctype,vcdays,vcdaysdw,empid,CountryID)
    		   VALUES ('Annual Leave',20,30,@empid,@id) -- this is not inserting the row 

    Please suggest.

    Friday, July 19, 2019 10:20 AM

Answers

  • User753101303 posted

    Previously you talked about binditid_pk in two tables and now you are back at discussing CountryID ?

    What is the problem with what you shown previously? If using :

    INSERT INTO Country_Master etc... 
    
    SET @bintid_pk = SCOPE_IDENTITY()
    --to insert "Annual Leave"  first time for the country
    INSERT INTO Leave_Type_Master (vctype,vcdays,vcdaysdw,empid,CountryID)
    VALUES ('Annual Leave',@vcdays,@vcdaysdw,@empid,@bintid_pk) 

    I don't see how the CountryID could have another value than the one you provided (but of course the bigid column will have its own value).

    At worst you could add after the INSERT :
    SELECT CountryID,@bintid_pk FROM Leave_Type_Master WHERE bigid=SCOPE_IDENTITY() and you should see that both columns have the same value.

    Edit: " the bintid_pk in first table is different from the bintid_pk in another table" which is not the same than having a problem with bintid_pk and CountryID ???? For now it seems some kind of confusion between maybe the value of CountryID and Leave_Type_Master.bigid ?

    Make sure to explain accurately the exact problem you see. Something such as below works as expected:

    DECLARE @a TABLE (pk INT IDENTITY (15,1),Data CHAR(1))
    DECLARE @b TABLE (pk INT IDENTITY,Data CHAR(1),fk INT NOT NULL)
    DECLARE @pk INT
    INSERT INTO @a(Data) VALUES('A')
    SET @pk=SCOPE_IDENTITY() -- expected 15
    INSERT INTO @b(Data,fk) VALUES ('Z',@pk) -- reuse this value as a fk
    SELECT *,SCOPE_IDENTITY() FROM @b -- all is fine but the pk for this table and SCOPE_IDENTITY() are 1 which is expected

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2019 10:52 AM
  • User475983607 posted

    Sir, why it is poor design?

    IMO, the schema is not normalized.  This causes complexity.

    Scope_Identity() has been around a long time and known to work. Clearly your first snippet was throwing exceptions but you did not tell us about the exceptions.  This indicates issues elsewhere in the code like an empty catch block.  

    Did you fix the bug mentioned above? Is there any way you can post the entire stored procedure and the C# code that invokes the stored procedure?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2019 11:33 AM

All replies

  • User753101303 posted

    Hi,

    With https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017 you tell when creating the table that a column should have its value being automatically incremented.

    You INSERT all the columns you need without providing a value for this one. You can then use SCOPE_IDENTITY() to retrieve the value generated by the INSERT.

    The other option is to use  "sequence numbers". Here I would expect :

    INSERT INTO Leave_Type_Master (vctype,vcdays,vcdaysdw,empid)
    VALUES ('Annual Leave',20,30,@empid) -- this is not inserting the row 
    SET @id = SCOPE_IDENTITY()

    but I suspect some other problem "seems weird if the id for this row is really CountryID ???

    If you need further help rather than telling that the row doesn't insert, tell what happens. I assume you have a message? They are intended to tell you want is wrong so do use them.

    Friday, July 19, 2019 10:37 AM
  • User1052662409 posted
    Yes, id in first table is CountyID in second table.
    After inserting Country in first table I want to use CountryID (which is auto incremented column in first table) in another table in same store procedure.
    Friday, July 19, 2019 3:09 PM
  • User77042963 posted

    Post your whole code and your table DDLs.

    Friday, July 19, 2019 3:28 PM
  • User1052662409 posted

    limno

    Post your whole code and your table DDLs.

    First Table where I am inserting country information

    CREATE TABLE [dbo].[Country_Master](
    	[bintid_pk] [bigint] IDENTITY(1,1) NOT NULL,
    	[vctitle] [varchar](50) NULL,
    	[Zone] [char](10) NULL,
    	[status] [bit] NOT NULL,
    	[dateadded] [datetime] NOT NULL,
    	[leave_half] [bit] NOT NULL,
    	[leave_Request_Approval] [bit] NOT NULL,
    	[Invoice_Step_Email] [bit] NOT NULL,
    	[currency] [nvarchar](50) NULL,
    	[leave_carry_forword] [float] NOT NULL,
    	[carry_leave_date] [nvarchar](max) NOT NULL,
    	[carry_leave_month] [nvarchar](max) NOT NULL,
    	[lapse_leave_date] [nvarchar](max) NOT NULL,
    	[lapse_leave_month] [nvarchar](max) NULL,
    	[Weekly_Off_Sunday] [bit] NOT NULL,
    	[Weekly_Off_Monday] [bit] NOT NULL,
    	[Weekly_Off_Tuesday] [bit] NOT NULL,
    	[Weekly_Off_Wednesday] [bit] NOT NULL,
    	[Weekly_Off_Thursday] [bit] NOT NULL,
    	[Weekly_Off_Friday] [bit] NOT NULL,
    	[Weekly_Off_Saturday] [bit] NOT NULL,
    	[Lapse_Leave_Apply] [bit] NOT NULL,
    	[leaveapproval] [char](1) NOT NULL,
    	[AVleave] [float] NOT NULL,
    	[Developleave] [float] NOT NULL,
    	[AVleaveCarryforward] [float] NOT NULL,
    	[DevelopleaveCarryforward] [float] NOT NULL,
    	[avleavelapse] [bit] NOT NULL,
    	[Devworkerleavelapse] [bit] NOT NULL,
    	[experiencedleave] [bit] NOT NULL,
    	[expleaveyear] [bigint] NOT NULL,
    	[expleave] [bigint] NOT NULL,
    	[childleave] [bit] NOT NULL,
    	[childleaveage] [bigint] NOT NULL,
    	[childleavecount] [bigint] NOT NULL,
    	[autoapproved] [bit] NOT NULL,
    	[autoapprovedtime] [bigint] NOT NULL,
    	[contractalertdays] [bigint] NOT NULL,
    	[procurementalertdays] [bigint] NOT NULL,
    	[expleavelimit] [bigint] NOT NULL,
    	[dw_lapse_leave_date] [nvarchar](max) NOT NULL,
    	[dw_lapse_leave_month] [nvarchar](max) NOT NULL,
    	[sickleavedays] [bigint] NOT NULL,
    	[Request_Role] [bit] NOT NULL,
    	[Invoice_Role] [bit] NOT NULL,
    	[leaveconsider4days] [bit] NOT NULL,
    	[training_book] [bit] NULL,

    Second table where I want the the bintid_pk to be inserted in to second table's CountryID column. Below is my second table.

    CREATE TABLE [dbo].[Leave_Type_Master](
    	[bigid] [bigint] IDENTITY(1,1) NOT NULL,
    	[vctype] [nvarchar](max) NULL,
    	[vcdays] [nvarchar](50) NULL,
    	[vcdaysdw] [nvarchar](50) NULL,
    	[noboundry] [bit] NOT NULL,
    	[sequence] [int] NOT NULL,
    	[empid] [bigint] NOT NULL,
    	[lastupdateid] [bigint] NOT NULL,
    	[dateadded] [datetime] NOT NULL,
    	[datemodi] [datetime] NOT NULL,
    	[CountryID] [bigint] NOT NULL,
    	[status] [bit] NOT NULL,
    	[considerholiday] [bit] NOT NULL,
    	[considerweeklyoff] [bit] NOT NULL,
    	[NPshow] [bit] NOT NULL,
    	[DWshow] [bit] NOT NULL,
    	[uploadoption] [bit] NOT NULL,

    And below is the store procedure where I am trying to do so as explained above.

    INSERT INTO Country_Master( vctitle,dateadded,leave_half,leave_Request_Approval,Invoice_Step_Email,currency,leave_carry_forword,carry_leave_date,carry_leave_month,lapse_leave_date,lapse_leave_month,Weekly_Off_Monday,Weekly_Off_Tuesday,Weekly_Off_Wednesday,Weekly_Off_Thursday,Weekly_Off_Friday,Weekly_Off_Saturday,Weekly_Off_Sunday,Lapse_Leave_Apply,leaveapproval,Developleave,DevelopleaveCarryforward,Devworkerleavelapse,experiencedleave,expleaveyear,expleave,childleave,childleaveage,childleavecount,autoapproved,autoapprovedtime,contractalertdays,procurementalertdays,expleavelimit,dw_lapse_leave_date,dw_lapse_leave_month,sickleavedays, Request_Role,Invoice_Role,leaveconsider4days,training_book)
    			VALUES (@vctitle,Getdate(),@leave_half,@leave_Request_Approval,@Invoice_Step_Email,@currency,@leave_carry_forword,@carry_leave_date,@carry_leave_month,@lapse_leave_date,@lapse_leave_month,@Weekly_Off_Monday,@Weekly_Off_Tuesday,@Weekly_Off_Wednesday,@Weekly_Off_Thursday,@Weekly_Off_Friday,@Weekly_Off_Saturday,@Weekly_Off_Sunday,@Lapse_Leave_Apply,@leaveapproval,@Developleave,@DevelopleaveCarryforward,@Devworkerleavelapse,@experiencedleave,@expleaveyear,@expleave,@childleave,@childleaveage,@childleavecount,@autoapproved,@autoapprovedtime,@contractalertdays,@procurementalertdays,@expleavelimit,@dw_lapse_leave_date,@dw_lapse_leave_month,@sickleavedays, @Request_Role,@Invoice_Role,@leaveconsider4days,@training_book)
    
    			SET @bintid_pk = SCOPE_IDENTITY()
    			--to insert "Annual Leave"  first time for the country
    			INSERT INTO Leave_Type_Master (vctype,vcdays,vcdaysdw,empid,CountryID)
    		   VALUES ('Annual Leave',@vcdays,@vcdaysdw,@empid,@bintid_pk) 

    Again to remove any confusion : -

    Firstly I am inserting a new country in Country_Master table as country inserted and the bintid_pk is created I want to insert it as CountryID in Leave_Type_Master table.

    For example ; If India is just inserted and it gets bintid_pk as 221 then it should insert into Leave_Type_Master's CountryID column  with value 221.

    Thanks

    Saturday, July 20, 2019 3:01 AM
  • User475983607 posted

    Wow, this is a very poor design but Scope_Identity() should function as expected.  I assume there is an error on the initial insert.  The code does not check for a successful insert and most likely you are not catching exceptions so you're missing the error.  Make sure you are properly handling exceptions as we cannot see this code.  Also use the Visual Studio debugger to step through your code.

    Anyway, I use the OUTPUT clause rather than SCOPE_IDENTITY().

    https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017

    Saturday, July 20, 2019 1:15 PM
  • User77042963 posted

    Thanks for posting your table DDL to help us to understand your issue.

    Your second insert to Leave_Type_Master will not work because you didn't provide many columns which are not NULLable columns.

    You can test out if you change all these columns to nullable (by removing not null in your table definition) and your insert will work.

    Monday, July 22, 2019 1:43 PM
  • User1052662409 posted

    this is a very poor design

    Sir, why it is poor design?

    Wednesday, July 24, 2019 4:03 AM
  • User1052662409 posted

    limno

    Your second insert to Leave_Type_Master will not work because you didn't provide many columns which are not NULLable columns.
    You can test out if you change all these columns to nullable (by removing not null in your table definition) and your insert will work.

    Now it is inserting but the bintid_pk in first table is different from the bintid_pk in another table and all other values are perfectly right.

    Why these bintid_pk and bintid_pk are not equal. For example in first table the CountryId is 10258 and in after selection scope_identity() it is 10328

    Wednesday, July 24, 2019 9:08 AM
  • User753101303 posted

    Which other table ? The two bintid_pk columns you are talking about are both identity values ? If yes they are just each generated and there is no reason for having the same value in both table.

    Else show the code that is supposed to make them both to have the same value. If SCOPE_IDENTITY is used at the correct place it should work.

    As a side note I perhaps added some confusion as at first I really didn't understood at all what happened and even that you actually had additional code. Rather than things such as "this is not inserting the row" always tell which error message you have so that we can better understand which problem you have and do the same at well (don't guess but do use error messages)

    Wednesday, July 24, 2019 9:37 AM
  • User1052662409 posted

    PatriceSc

    Which other table ?

    means table Leave_Type_Master.

    I am inserting a row in  table Country_Master. Once the row inserted I want the first column value which is auto incremented (named bintid_pk) and at just after insertion in Country_Master , I want to insert that auto incremented value (named bintid_pk) into another table Leave_Type_Master column which is CountryID.

    And If the value of bintid_pk is 1023 in Country_Master then is should be the same 1023  be there in Leave_Type_Master's CountryID column.

    Like below

    First Step :- I am inserting row into a table A.

    Second Step : - I need to select the auto incremented id for the table A for the recent inserted row.

    Third Step : I want to insert the same id in second table B

    Do I need to elaborate more, please let me know.

    Wednesday, July 24, 2019 10:14 AM
  • User753101303 posted

    Previously you talked about binditid_pk in two tables and now you are back at discussing CountryID ?

    What is the problem with what you shown previously? If using :

    INSERT INTO Country_Master etc... 
    
    SET @bintid_pk = SCOPE_IDENTITY()
    --to insert "Annual Leave"  first time for the country
    INSERT INTO Leave_Type_Master (vctype,vcdays,vcdaysdw,empid,CountryID)
    VALUES ('Annual Leave',@vcdays,@vcdaysdw,@empid,@bintid_pk) 

    I don't see how the CountryID could have another value than the one you provided (but of course the bigid column will have its own value).

    At worst you could add after the INSERT :
    SELECT CountryID,@bintid_pk FROM Leave_Type_Master WHERE bigid=SCOPE_IDENTITY() and you should see that both columns have the same value.

    Edit: " the bintid_pk in first table is different from the bintid_pk in another table" which is not the same than having a problem with bintid_pk and CountryID ???? For now it seems some kind of confusion between maybe the value of CountryID and Leave_Type_Master.bigid ?

    Make sure to explain accurately the exact problem you see. Something such as below works as expected:

    DECLARE @a TABLE (pk INT IDENTITY (15,1),Data CHAR(1))
    DECLARE @b TABLE (pk INT IDENTITY,Data CHAR(1),fk INT NOT NULL)
    DECLARE @pk INT
    INSERT INTO @a(Data) VALUES('A')
    SET @pk=SCOPE_IDENTITY() -- expected 15
    INSERT INTO @b(Data,fk) VALUES ('Z',@pk) -- reuse this value as a fk
    SELECT *,SCOPE_IDENTITY() FROM @b -- all is fine but the pk for this table and SCOPE_IDENTITY() are 1 which is expected

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2019 10:52 AM
  • User475983607 posted

    Sir, why it is poor design?

    IMO, the schema is not normalized.  This causes complexity.

    Scope_Identity() has been around a long time and known to work. Clearly your first snippet was throwing exceptions but you did not tell us about the exceptions.  This indicates issues elsewhere in the code like an empty catch block.  

    Did you fix the bug mentioned above? Is there any way you can post the entire stored procedure and the C# code that invokes the stored procedure?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 24, 2019 11:33 AM
  • User1052662409 posted

    .

    Wednesday, July 24, 2019 11:59 AM
  • User1052662409 posted

    At worst you could add after the INSERT :
    SELECT CountryID,@bintid_pk FROM Leave_Type_Master WHERE bigid=SCOPE_IDENTITY() and you should see that both columns have the same value.

    Thank you PatriceSc.

    Wednesday, July 24, 2019 12:04 PM