Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: Insert batch with incrementing ID

    I have started to re-write a stored proc that used to use a cursor. To avoid using the cursor, I've made the insert statement that is the primary function of the code set based. My big problem though is that the primary key that I am inserting is taken from another table and incremented for each entry and more importantly, the table that stores the key is also updated so that the key is not used again. This is where the problem lies. Because it is set based rather than a loop, I can't update as I go. I tried using a UDF to get the number, but these can't update, only select, so I am a bit stuck. Is my only option to use extended stored procs, a DTS job or to revert to using a while loop? What about using a before insert trigger and adding the primary key that way? Just after thoughts really!

    Code:
    	insert into docsadm.needs_indexing(system_id, status, status_date, retry_count, docnumber, ft_system_id) select dbo.fnc_GetKey(), 1, getdate(),0,docnumber,@ftsystem_id from docsadm.profile where LDA_PROCESSED='N' and fulltext='Y' and convert(varchar,creation_time ,108) < @createdate
    Thanks,
    Matt.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Matt, Look up Identity columns and/or Sequences and see if either of these can help you. Basically, these items are generated by the system and increment by 1 (as a default but you can change it).

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    You could use the ROW_NUMBER() function in your INSERT... SELECT:

    ROW_NUMBER (Transact-SQL)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •