Unanswered: Inserting into table with one column 1,2,3,4...
I have a temporary table into which I insert bulk data from a stored procedure output. It will be easy for me if the temptable can have a column with sequential numbers 1,2,3,4,. I could not figure out a way to do it. This is the stripped down sql what I have in the stored procedure.
insert into temptable select name, email, date from usertable where condition = xyz.
I tried auto increment column with seed 1, increment 1. But if I delete and insert again it does not start from 1. Is there any way to accompolish this? It will make the front-end code very simple.
if you use the DELETE statement to remove Data from your table SQL Server the auto increment column will not start at 1 but counting on at the last values.
Use TRUNCATE TABLE to remove all data from a table. After that auto increment column starts at 1 again when you insert new data.
Note: With Truncate Table you will remove all data! There is no way to select data conditional.
Why not use an identity column instead? By doing so, activity of maintaining the sequence is left to SQL Server. Then, when a record is deleted, just renumber the sequence so that there wouldn't be any gaps between sequence number.
However, since you would be renumbering the identity column, dont use that column as reference column for other tables.