locked
Generate Reference No Automtically If record Insert RRS feed

  • Question

  • User2033107836 posted

    Hello

    I have One Order Details Table

    If any record insert it has to create automtically Reference no order wise

    For example

    R00001

    R00002

    R00003

    So on..

    How to do using SQL.

    Thanking You

    Monday, October 23, 2017 6:26 AM

Answers

  • User347430248 posted

    Hi asp.ambur,

    you can try to generate sequence. it will generate next number automatically and then you just need to append "R" before value.

    example:

    --Create the Test schema  
    CREATE SCHEMA Test ;  
    GO  
    
    -- Create a table  
    CREATE TABLE Test.Orders  
        (OrderID int PRIMARY KEY,  
        Name varchar(20) NOT NULL,  
        Qty int NOT NULL);  
    GO  
    
    -- Create a sequence  
    CREATE SEQUENCE Test.CountBy1  
        START WITH 1  
        INCREMENT BY 1 ;  
    GO  
    
    -- Insert three records  
    INSERT Test.Orders (OrderID, Name, Qty)  
        VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;  
    INSERT test.Orders (OrderID, Name, Qty)  
        VALUES (NEXT VALUE FOR Test.CountBy1, 'Seat', 1) ;  
    INSERT test.Orders (OrderID, Name, Qty)  
        VALUES (NEXT VALUE FOR Test.CountBy1, 'Brake', 1) ;  
    GO  
    
    -- View the table  
    SELECT * FROM Test.Orders ;  
    GO  

    Output:

    Reference:

    Sequence Numbers

    CREATE SEQUENCE (Transact-SQL)

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 23, 2017 7:46 AM