none
How to Populate a field in a table with auto numbers? RRS feed

  • Question

  • Hello team,

    Please remember the field is not a primary key. Just I want to have numbers as 1, 2, 3 and so on.

    Regards,

    Sky


    CloudsInSky

    Monday, December 11, 2017 9:35 PM

Answers

  • have a column defined as an "identity", doesn't have to be a primary key 

    <column name> int identity(1,1)

    the first "1" is the seed value and the second "1" is the increment

    • Marked as answer by cloudsInSky Monday, December 18, 2017 2:47 AM
    Monday, December 11, 2017 9:40 PM

All replies

  • have a column defined as an "identity", doesn't have to be a primary key 

    <column name> int identity(1,1)

    the first "1" is the seed value and the second "1" is the increment

    • Marked as answer by cloudsInSky Monday, December 18, 2017 2:47 AM
    Monday, December 11, 2017 9:40 PM
  • Try Sequence, https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql

    CREATE SEQUENCE Test.CountBy1  
        START WITH 1  
        INCREMENT BY 1 ;  
    GO  
    
    


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



    • Edited by Sarat (SS) Monday, December 11, 2017 9:56 PM
    Monday, December 11, 2017 9:55 PM
  • CREATE TABLE [dbo].[TestTable](
     [Col1] [int] IDENTITY(1,1) NOT NULL,
     [Col2] ...
    )

    A Fan of SSIS, SSRS and SSAS

    Monday, December 11, 2017 10:12 PM
  • as mentioned above you can choose any column and enable identity. then you can use seed value and incremental value. condition column have to be integer data type.

    I am not sure what exactly you are trying to do here. if you want your row to be uniquely identified with out any external feed, you can consider defaulting that column then your solution will be a column with unique identifier and then feed with new id.

    check

    Tuesday, December 12, 2017 12:50 AM
  • How?

    CloudsInSky

    Tuesday, December 12, 2017 5:48 AM
  • What do you mean by saying defaulting? Thanks skey

    CloudsInSky


    Do your mean newif() function?
    • Edited by cloudsInSky Tuesday, December 12, 2017 5:51 AM
    Tuesday, December 12, 2017 5:49 AM
  • Itzik Ben-Gan wrote great (efficient) script

    CREATE FUNCTION GenerateSequence (@start INT, @end INT)
    RETURNS TABLE
    AS
    RETURN
    WITH
    Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
    Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
    Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
    Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
    Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y),
    Num6 (n) AS (SELECT 1 FROM Num5 AS X, Num5 AS Y),
    Nums (n) AS
    (SELECT ROW_NUMBER() OVER(ORDER BY n)
      FROM Num6)
    SELECT n FROM Nums
    WHERE n BETWEEN @start AND @end;

    GO

    INSERT INTO yourtable (num) SELECT n FROM GenerateSequence(1, 10000);


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Xi Jin Tuesday, December 12, 2017 7:08 AM
    Tuesday, December 12, 2017 6:44 AM
    Answerer
  • Hi Sky,

    In your scenario, you want to add an extra incremental column in your table. Right?

    As mentioned above, you can simply add a identity column. Please refer to following simple sample:

    create table Test 
    (
    Item varchar(10),
    Val int
    )
    
    insert into Test values
    ('A',10),
    ('B',12),
    ('C',20),
    ('D',15),
    ('E',30),
    ('F',21)
    
    select *
    from Test 
    
    ALTER TABLE Test
      ADD ID INT IDENTITY(1,1) NOT NULL
    
    select *
    from Test 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 12, 2017 7:18 AM
  • How?

    CloudsInSky

    Monday, December 18, 2017 2:49 AM