Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Inserting into table with one column 1,2,3,4...

    Hi,

    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.

    Thank you

  2. #2
    Join Date
    Jan 2005
    Posts
    8
    Hi,

    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.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is there any column or group of columns that will be unique?

    -PatP

  4. #4
    Join Date
    Jan 2005
    Posts
    10
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •