SQL INSERT

[judyscofield]

I'd like to insert into a table from another table, but include a sequence number that increments by one for each inserted record. The sequence number does not exist in the old table. How do I code this.

Ideally, {'d like the sequence number to restart each time another value changes.

Judy Scofield

 

[ClaireHCM]

--You could use

alter table new_table add column_name int identity(1,1)

/*and then insert into newtable from old table which doesnt have sequence number.*/

What is " sequence number to restart each time another value changes"?

 

[swampBoogie]

declare @changeCol varchar(10)
declare @seqno int = 1

insert into t2(c1,seqno)
 select c1,@seqno = case when @changeCol = c1 then
  @seqno + 1 else 1 end,@changecol = c1 from t1

 

[judyscofield]

Thanks - both of these suggestions helped me solve this problem.