Member Avatar for 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 `

  • 3 Contributors
  • 2 Replies
  • 111 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by tsmorris
Member Avatar for 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.

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.