none
Getting back the identities in bulk inserted rows

    Question

  • Hi all,

    I am facing a problem and haven't been able to figure out how to do it (tried a long time and googled it with no luck). I want to insert a bulk rows in a table with an identity in it, like this:

    ID(identity)     LEVEL(Decimal)          VOLUME(Float)

    1                    1.1                            100

    2                    1.2                            200

    3                    1.3                            300

    ...

    I have a gridview to display these table (it does not display the IDs). The gridview displays 10 rows so that the user can enter the 10 rows at once, as shown here (the link will open in a new window). The first time the user hits on Save, it is ok. the problem is when the user hits a second time to update the recently saved rows. How can I get back the bulk identities that have just been created so that the user can update the rows immediately after the first save?

    There is the OUTPUT clause in Sql Server to save in a temp table (@ins ) with the recently rows as explained here, but I do not know how to get back that table from c#. The stored procedure I created is:

    CREATE

     

    PROCEDURE TankInsertGauge

     

    @TANK_ID INTEGER = NULL,

     

    @MEASUREMENT_UNIT_ID INTEGER = NULL,

     

    @LEVEL_UNIT CHAR(2) = NULL,

     

    @LEVEL DECIMAL(4,2) = NULL,

     

    @VOLUME FLOAT = NULL

    AS

     

    SET NOCOUNT ON

     

    Declare @ins as table(

     

    InsID int,

     

    InsTANK_ID int,

     

    InsMEASUREMENT_UNIT_ID int,

     

    InsLEVEL_UNIT char(2),

     

    InsLEVEL DECIMAL(4,2),

     

    InsVOLUME FLOAT

     

    )

     

    INSERT INTO "GAUGE" ("TANK_ID",

     

    "MEASUREMENT_UNIT_ID",

     

    "LEVEL_UNIT",

     

    "LEVEL",

     

    "VOLUME")

     

    Output inserted.ID,inserted.TANK_ID, inserted.MEASUREMENT_UNIT_ID,inserted.LEVEL_UNIT,

     

    inserted.LEVEL, inserted.VOLUME into @ins

     

    VALUES (@TANK_ID,

     

    @MEASUREMENT_UNIT_ID,

     

    @LEVEL_UNIT,

     

    @LEVEL,

     

    @VOLUME)

     

     

    SELECT * FROM @ins

     

     

    RETURN 0

    GO

    Any help supplied is highly appreciated,

    Marco

    • Moved by Larcolais Gong Tuesday, February 08, 2011 8:23 AM (From:Visual C# General)
    Saturday, February 05, 2011 5:38 PM

Answers

All replies

  • Hi MarcoViY,

    Thank you for posting.

    Could you please descript what is the problem when the user clicked the second time?

    Best Regards,

    Larcolais


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 07, 2011 8:49 AM
  • Thanks Larcolais for your reply. The problem is that as the rows are being inserted with identity IDs, and the second time the user saves, he or she is actually updating the recently inserted rows. So, I need to recover the values of the IDs in every row just inserted. Recovering the id inserting only one row is done with

     @ID INT = NULL OUTPUT

    SELECT  @ID = @@IDENTITY

     

     

     

     

     

     The problem is how to recover the id values for all the inserted rows (bulk insertion).

    Thanks,

    Marco

    Monday, February 07, 2011 2:24 PM
  • Hi MarcoViY,

    Thank you for your feedback.

    It looks that your question is more like SQL. Ill help you moving your thread into SQL forum.

    Hope this helps.

    Best Regards,

    Larcolais


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 08, 2011 8:22 AM
  • Assuming you were inside a transaction you can record the highest existing
    identity value before and after the bulk insert and derive the list of
    numbers.

    Tuesday, February 08, 2011 8:33 AM
  • That is an excellent idea Praktikant. Thank you very much for the hint,

    Marco

    Tuesday, February 08, 2011 1:57 PM
  • Assuming you were inside a transaction you can record the highest existing
    identity value before and after the bulk insert and derive the list of
    numbers.


    No!  This does not work correctly without some kind of caveat!  Which version of SQL Server are you using?

    EDIT:

    ( Toned down a litte. )

    Tuesday, February 08, 2011 2:01 PM
    Moderator
  • I am using sql server 2008. Why doesn't it work?
    Tuesday, February 08, 2011 2:21 PM
  • I am using sql server 2008. Why doesn't it work?


    If another person process does an insert into the table while you are doing the multi-row insert, you will have holes in the identity list that were taken by the other process.  If you must do something like this and must use a process like this -- that is, projecting what values will be inserted -- you must use the table lock hint -- does it need to be an exclusive table lock hint? -- to assure that there will be no holes in the identities list.  This itself will not be an option if the table must remain available "for normal business".

    The standard method of getting the identity list with SQL Server 2008 is:

    • create a temporary table or table variable to house the identity list
    • Use the OUTPUT clause when inserting records to save the information to the temp table

     

    Tuesday, February 08, 2011 2:29 PM
    Moderator
  • I tried the OUTPUT clause, as explained in the beginning of this thread, but the problem I faced then was how I get back the temp table values from c#. Could you please help me?

    Thanks,

    Marco

    Tuesday, February 08, 2011 2:55 PM
  • if object_id('TempDB..#OutputIDs','U') IS NOT NULL drop table #OutputIDs

    create table #OutputIDs (ID int)

    insert into myMainTable (Field1, Field2, Field3)

    OUTPUT Inserted.ID into #OutputIDs

    select Field1, Field2, Field3 from myBulkInsertTable

    select * from #OutputIDs

     

    and get back the results using either DataReader or DataAdapter. (e.g. use ExecuteQuery method for DataReader or DA.Fill method to get back a DataSet)


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, February 08, 2011 3:17 PM
    Moderator
  • You aren't doing a bulk insert, you are inserting one record at a time.  The proc you are calling from code returns a recordset even though it is doing an insert, why can't you treat it as any proc call that returns data to you?
    Tuesday, February 08, 2011 3:18 PM
  • Gao,

    Why do you say I am not doing a bulk insert? I am passing a dataset with a table with some rows in it to the dataadapter, in this way:

    dataAdapter.Update(tankData, GAUGE_TABLE);

    GAUGE_TABLE has some rows in it and all rows are being inserted.

    So, even if I call dataAdapter.Update, I will have a recordset returned? How do I recover the recordset from my c# program?

    Thanks,

    Marco

     

    Tuesday, February 08, 2011 5:06 PM
  • The proc you posted will only insert one record at a time, is why it is not bulk operation.  You need an output parameter for datadapter.update

     

    When using Update, the order of execution is as follows:

    1. The values in the DataRow are moved to the parameter values.

    2. The OnRowUpdating event is raised.

    3. The command executes.

    4. If the command is set to FirstReturnedRecord, then the first returned result is placed in the DataRow.

    5. If there are output parameters, they are placed in the DataRow.

    6. The OnRowUpdated event is raised.

    7. AcceptChanges is called.

      http://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspx

      Output parameter http://msdn.microsoft.com/en-us/library/ms378108(v=sql.90).aspx

    • Proposed as answer by Naomi NModerator Tuesday, February 08, 2011 6:43 PM
    • Marked as answer by KJian_ Friday, February 11, 2011 9:28 AM
    Tuesday, February 08, 2011 5:41 PM
  • Why do you say I am not doing a bulk insert? I am passing a dataset with a table with some rows in it to the dataadapter, in this way:

    dataAdapter.Update(tankData, GAUGE_TABLE);

    GAUGE_TABLE has some rows in it and all rows are being inserted.

    So, even if I call dataAdapter.Update, I will have a recordset returned? How do I recover the recordset from my c# program?


    You originally posted this in a C# forum, right? And then you were diverted here. Oh, well, while this is a T-SQL forum, we should try to set you straight on your C# programming.

    Since you are on SQL 2008 you can use table-valued parameter, which permits you to pass a DataTable a List<SqlDataRecord> or a IDataReader. I have an article on my web site that describes this: http://www.sommarskog.se/arrays-in-sql-2008.html

    That article does not include getting the identity values back, though. But as said in the thread, you can use the OUTPUT clause for that. Rather than using DataAdapter.Update, you would need to use DataAdapter.Fill or an ExecuteReader and then update the original dataset. You may have to write more code, but there will be less magic things happening behind your back.

    But I repeat the disclaimer: this is a T-SQL forum, and myself I only program in C# occassionally.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Tuesday, February 08, 2011 10:40 PM