auto numbering in sql server

[saleem.mukhtiar]

Dear Friends i want to copy data from one table to another table i know we can do this task using following query

INSERT INTO Destination
            (EnrollNo,
             CheckIn,
             CheckOut,
             DiffTime)
SELECT EnrollNo,
       CheckIn,
       CheckOut,
       DiffTime
FROM   Source 

but here is problem ... i want to add a new colum with roll number which is started from 1 to end ... i will give a PK with autonumbreing ... after this 2nd colum with autonumbering without PK but it should be start from 1 to end of running query ..
Thanks in advance `

[deceptikon]

You can have an identity column with autoincrement properties that isn't the primary key. In the new table, just create the new column, set it to identity "Yes", and with increment step and seed of 1.

[tsmorris]

When you create your new table, create your columns for autonumbering with the option IDENTITY. It sounds like you want to start at 1 and increment by 1 so you don't need to add the options for seed or increment.

When you insert into the table, don't specify the identity columns in your column list, the system will generate values for those columns.

You might want to use a ORDER BY on your SELECT statement to ensure that your data is in the order you expect.