none
How do I insert a sequence into a Identity column

Все ответы

  • Easiest is probably to create a new column as an identity.

    You could create a trigger but the issue there is catering for multi-row updates and that you can't use max on the column due to the existing value. What would you do if an existing value need to be used for a new row?

     

    10 октября 2011 г. 16:21
  • There will Multi_row updates in the millions, also the column has to have unique incremental values for every insertion
    10 октября 2011 г. 16:30
  • You need to give the initial value (seed value) to the identity field which will be equal the number of records in a table + 1.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    10 октября 2011 г. 16:33
  • There's no good way to guarantee that the incremental values will be maintained in time when records are deleted in time.  If you really need incremental values you're better off selecting a Row_Number with the rows when you need that sequence than to try to maintain it in data.  If you just need unique values that are mostly sequential then an INT IDENTITY column would best work for that.

    Row_Number sample:

    SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode 
    
    FROM Sales.vSalesPerson
    
    WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

     

    INT IDENTY sample:

     

    CREATE TABLE myTable

    (ID as INT IDENTITY PRIMARY KEY,

    MyFieldName VARCHAR(50)

    )

     

    INSERT INTO myTable

    (myFieldName)

    SELECT

    'Test 1'

    UNION ALL SELECT

    'Test 2'

    UNION ALL SELECT

    'Test 3'

    UNION ALL SELECT

    'Test 40'

    UNION ALL SELECT

    'Test 30'

     

    --now delete a row

    DELETE FROM MyTable Where MyFieldName = 'Test 3'

     

    --Select everything and add in a row number too...

    SELECT *,  ROW_NUMBER() OVER(ORDER BY ID) AS 'Row Number'

    FROM MyTable

     


    Eric Isaacs


    • Изменено Eric Isaacs 10 октября 2011 г. 17:57
    10 октября 2011 г. 17:52
  • I cant modify existing table, Unless I have to drop and recreate a temp table , then move datata from old table to new one that has an Identity Column
    10 октября 2011 г. 19:28
  • That may be a solution for your case as well.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    10 октября 2011 г. 19:39
  • But the point is how do I maintain previous entries already in the Column ,  some are NULL  Values, and I want to continue inserting lets say from the 50th row, 51... for new entries
    10 октября 2011 г. 19:50
  • I don't think IDENTITY column can accept NULLs. However, you can insert old values if you turn off the identity insert. 
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    10 октября 2011 г. 20:05
  • Please how do you think the trigger will work?

    I hope it can solve the issue

    := ( while select count (*)  from [table]  = (value),

    start inserting data into the column starting from value + 1 )

    11 октября 2011 г. 18:31
  • Won Jondee,

    I am not aware which version of SQL Server you are having, But in SQL Server 2012 we do have a new features called Sequences.


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    9 июня 2012 г. 13:00