none
how to retrieve inserted rows in sql server 2000

    Question

  • Hi,

    I have this scenario:

    items should be inserted into a item table before they are converted into assets. The database server is sql server 2000.

    so item-> itemTable ->assetTable

    When I insert items into the itemTable, how can I retrieve the itemID (an identity column) that will be created during the insert?

    I know I can use scope_identity(), but if two or three person are inserting items to the same table, which scope_identity() correctly return the identity value? Should I lock the table first?

    Also, I am using openxml to insert more than one items in one tsql statement, is there any chance to get all the newly created identity values? To make things worse, some items do not need to be converted into assetTables (according to itemType column), how can I loop through the whole xml and only insert certain items (continue using openxml if possible).?

    many thanks

    jerry

     

    Wednesday, May 03, 2006 2:45 PM

Answers

  • Unfortunately there is no easy way to get the generated identity values for multiple rows other than querying the table or using a trigger to persist the results. SCOPE_IDENTITY or @@IDENTITY will only give the last generated value and single row only. In SQL Server 2005, there is a new OUTPUT clause that can be used in DML statements to get the identity values into a temporary table for example. See post below for more details on this feature or Books Online:
     
     
    So getting back to SQL Server 2000, you can do the following:
     
    -- in your SP:
    create table #ident ( i int not null )
    insert ...
    select ...
    from openxml()
     
    -- in your trigger
    if object_id('tempdb...#ident') is not null
    insert into #ident (i)
    select identity_col from table
     
     
    Now you can query the temporary table to get the generated values. You will probably need other key column(s) to match the rows with the ones returned by OPENXML.
    Friday, May 05, 2006 12:11 AM

All replies

  • To answer your first question, Scope_Identity() is designed for just this situation.  No need to lock the table.

    I've not used openxml so can't help you with that.  You could do this through DTS easily.

    On the bulk insert, I suspect you'll have to re-query after the insert to get the values.

    Wednesday, May 03, 2006 3:15 PM
  • How can I use DTS to do this? Can you give me an example in c# (or vb)?

    Cheers

    Wednesday, May 03, 2006 3:22 PM
  • DTS is a feature of SS2000 Enterprise Manager.

    How you do this depends upon exactly what you want to do.

    For the absolute basics, you could use the DTS Wizard to create a simple transformation / import from the XML file to a SQL Server table.

    Not knowing your situation, I'd suggest one of two approaches:

    1) Import the data into a staging table, then insert into your two tables with a SQL statement based upon your desired criteria.

    2) Import the data into the first table with a timestamp (this would require adding a column to this table).  Insert into the second table with a SQL statement based upon your desired criteria and the timestamp.

    Wednesday, May 03, 2006 3:48 PM
  • Unfortunately there is no easy way to get the generated identity values for multiple rows other than querying the table or using a trigger to persist the results. SCOPE_IDENTITY or @@IDENTITY will only give the last generated value and single row only. In SQL Server 2005, there is a new OUTPUT clause that can be used in DML statements to get the identity values into a temporary table for example. See post below for more details on this feature or Books Online:
     
     
    So getting back to SQL Server 2000, you can do the following:
     
    -- in your SP:
    create table #ident ( i int not null )
    insert ...
    select ...
    from openxml()
     
    -- in your trigger
    if object_id('tempdb...#ident') is not null
    insert into #ident (i)
    select identity_col from table
     
     
    Now you can query the temporary table to get the generated values. You will probably need other key column(s) to match the rows with the ones returned by OPENXML.
    Friday, May 05, 2006 12:11 AM
  • Fantastic... I have been prowling through my new O'Reilly textbook looking for this type of feature (as I need it - adding new stock items to an e-commerce database) and I discover that Microsoft have JUST invented it and implemented it into TSQL and SQL Server 05?!
    Microsoft has new-found respect from me...

     

    Tuesday, March 20, 2007 1:31 PM