SQL auto numbering problem

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 `

Posted 13-Dec-12 19:15pm

saleem.mukhtiar

Add a Solution

Comments

__TR__ 14-Dec-12 1:32am

What's the use of having a second column with auto-numbering when you already have a PK column with auto-numbering ?

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

saleem.mukhtiar · Answer 1 · 2012-12-13T19:39:00

Solution 4

Friends auto increment will be imposed on PK from SQL server ... other column of number will be start from 1 to end of the query ...
means ... i transfer 20 record from one table to another table last PK will be 20
and last record of 2nd column = 20

after this query i have transfer another 10 record here pk will be 30
but 2nd column will start from 1 to 10
...

Posted 13-Dec-12 19:39pm

saleem.mukhtiar

Comments

StackQ 14-Dec-12 1:40am

u r right.

__TR__ 14-Dec-12 2:09am

This is not an answer. Use "Have a question or comment?" button if you want to add any specific comments or questions and delete this solution to avoid getting downvoted.
To answer your question you can use ROW_NUMBER[^] to generate consecutive numbers.
Check this link[^]

Hetal Jariwala · Answer 2 · 2012-12-13T19:19:00

Solution 1

In Identity Specification change identity seed value by the number you want

Posted 13-Dec-12 19:19pm

Hetal Jariwala

Krunal Rohit · Answer 3 · 2012-12-13T19:20:00

Solution 2

http://www.w3schools.com/sql/sql_autoincrement.asp[^]
http://msdn.microsoft.com/en-us/library/ms186775.aspx[^]
http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/4f7a260e-9c57-423d-b899-4579061bf53c/[^]

Posted 13-Dec-12 19:20pm

Krunal Rohit

StackQ · Answer 4 · 2012-12-13T19:28:00

Solution 3

u can use this->

select * into UrNewTable_Name from SourceTable_Name;

to copy all data from SourceTable to NewTable

then u can add column as->

ALTER TABLE table_name
ADD column_name datatype;

Posted 13-Dec-12 19:28pm

StackQ