increment existing db columns

Question

I am trying to make a exact copy of a table but change the values of 2 columns. Basically I have 800 entries in the db table and need 800 more but i need the value of one of the columns (Set_id to be 11) and I need the value of another to auto increment...but the column isnt identity insert unfortunately. I created a temp table and copied existing table into temp table. I then am trying to write the temp table back to the original table with that one column as an 11 and trying to make the doctype id column start with 860 and then each entry auto increment with one. I created this cursor:

declare @id int, @document char(30)
select @id = 860
declare documents cursor for
/* This will select all Non-Physician users */
  select tag from cabinet..document_names

open documents

fetch next from documents into @document
while @@fetch_status <> -1
begin
 select @id = @id +1
      if not exists (select * from cabinet..document_names where set_id='11' and tag=@document)
      insert into cabinet..document_names (TAG, TAGORDER, ACTIVE,QUEUE, REASON, FORM,DIRECT_VIEW,GLOBAL,FU_SIGN,SIGN_X,
SIGN_Y,SIGN_W,SIGN_H,ANNOTATE,doctype_id,CODE,CALCTABLE_ID, DOC_TYPE,SET_ID,SUSPEND_DELAY,Text_Editing,Restrict_Viewing,Viewing_Expire,
Viewing_Period , DocHdrLength,DocHdrSearchString,DocFtrLength,DocFtrPageNo,DocRuleId,Outbound,SigQueue,SigReason)
select TAG, TAGORDER, ACTIVE,QUEUE, REASON, FORM,DIRECT_VIEW,GLOBAL,FU_SIGN,SIGN_X,
SIGN_Y,SIGN_W,SIGN_H,ANNOTATE,@ID,CODE,CALCTABLE_ID, DOC_TYPE,'11',SUSPEND_DELAY,Text_Editing,Restrict_Viewing,Viewing_Expire,
Viewing_Period , DocHdrLength,DocHdrSearchString,DocFtrLength,DocFtrPageNo,DocRuleId,Outbound,SigQueue,SigReason from
cabinet..document_names_temp 


fetch next from documents into @document

end
close documents 
deallocate documents

It is doing exactly what I want except the doctype id repeats itself as 861, 861 etc etc. I need that number to increment by one after each entry into original table. Any help on where I am messing up is apprecaited! thanks!

Answer 1

Shouldn't

  INSERT INTO cabinet..document_names ([columns])
  SELECT [columns] 
  FROM cabinet..document_names_temp 

have a WHERE statement as well? something like

  INSERT INTO cabinet..document_names ([columns])
  SELECT [columns] 
  FROM cabinet..document_names_temp 
  WHERE TAG = @Document

Currently your insert just inserts each record it can find in document_names_temp with doctype_id set to @ID which would explain the repetition of 861, 861, 861.. you probably have the same amount of records with docttype_id 862 as well.