none
Add Text with Sequence No in a column

    질문

  • Friends,

    I have a column called "tranid" and table contains around 1200 rows.

    Now i want to update the said column with sequence number like as follows;

    IVR001

    IVR002

    IVR003

    IVR004

    IVR005

    ....

    IVR1200

    Please guide me to update the column.

    Thanks & Regards,



    • 편집됨 Gunapriyan 2016년 11월 18일 금요일 오전 10:15
    2016년 11월 18일 금요일 오전 10:13

답변

  • I had tried as below as per your advise, But it throws err message;
    with Sequence as (
    SELECT *, Seq= row_number() over (order by date_of_entry)
      from tbl_ivtransaction
    )
    UPDATE Sequence
      set tran_id= 'IVR' + Right('000' + Cast(Seq as varchar(4)), 4);

        José Diz     Belo Horizonte, MG - Brasil


    • 답변으로 표시됨 Gunapriyan 2016년 11월 18일 금요일 오후 1:14
    2016년 11월 18일 금요일 오후 1:09

모든 응답

  • Try

    -- code #1 v2
    with Sequence as (
    SELECT *, Seq= row_number() over (order by column_name)
      from Tab
    )
    UPDATE Sequence
      set tranid= 'IVR' + Right('000' + Cast(Seq as varchar(4)), 4);


        José Diz     Belo Horizonte, MG - Brasil


    • 편집됨 José Diz 2016년 11월 18일 금요일 오후 1:09
    2016년 11월 18일 금요일 오후 12:47
  • Dear Jose,

    I had tried as below as per your advise, But it throws err message;

      

    with Sequence as (
    SELECT *, Seq= row_number() over (order by date_of_entry)
      from tbl_ivtransaction
    )
    UPDATE tbl_ivtransaction
      set tran_id= 'IVR' + Right('000' + Cast(Seq as varchar(4)), 4);

    ---------------------

    Here " Cast(Seq as varchar(4)), 4);" error msg throws like "

    Msg 207, Level 16, State 1, Line 6
    Invalid column name 'Seq'.

    ----------------

    Please help...

    2016년 11월 18일 금요일 오후 12:57
  • I had tried as below as per your advise, But it throws err message;
    with Sequence as (
    SELECT *, Seq= row_number() over (order by date_of_entry)
      from tbl_ivtransaction
    )
    UPDATE Sequence
      set tran_id= 'IVR' + Right('000' + Cast(Seq as varchar(4)), 4);

        José Diz     Belo Horizonte, MG - Brasil


    • 답변으로 표시됨 Gunapriyan 2016년 11월 18일 금요일 오후 1:14
    2016년 11월 18일 금요일 오후 1:09
  • Thank you very much boss.....
    2016년 11월 18일 금요일 오후 1:14
  • Sir,

    Now i have received the following error while execute the following query;

    with Sequence as (
    SELECT *, Seq= row_number() over (order by date_of_entry)
      from tbl_ivtransaction
    )
    UPDATE Sequence
      set tran_id= 'IVR' + Right('000' + Cast(Seq as varchar(4)), 4);


    Msg 8115, Level 16, State 2, Line 1
    Arithmetic overflow error converting expression to data type varchar.
    The statement has been terminated.

    Please help


    • 편집됨 Gunapriyan 2018년 6월 11일 월요일 오후 1:05 Forgot to include query
    2018년 6월 11일 월요일 오후 1:04
  • What is the length of your tran_id column?
    2018년 6월 11일 월요일 오후 1:45
    중재자
  • Arithmetic overflow error converting expression to data type varchar.

    At the beginning of this topic you mentioned the existence of 1,200 rows. but there are probably more than 9,999 rows in the tbl_ivtransaction table. What is the current number of rows in the tbl_ivtransaction table?

    The suggested code was to be run once, to update the contents of the tran_id column. It is not a code to run on a daily basis. Was there any reason to run it again?


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    • 편집됨 José Diz 2018년 6월 11일 월요일 오후 7:02
    2018년 6월 11일 월요일 오후 2:47
  • Another way is to use a computed column based on identity

    http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 11일 월요일 오후 4:19
  • Sir,

    Now it is crossing 12000 Rows and length of tran id is 8 digit now ie. "IVR12130"

    Actually, the big problem is suddenly the particular tran id column is null when somebody insert the values..

    But surprisingly all other cell are filling except tran id...thats why i have run the sql query using your query.

    So please help me to fix the said above query mean time, i will check my code why the tran id is blank while fall the data.

    Thanks & Regards,


    • 편집됨 Gunapriyan 2018년 6월 13일 수요일 오후 1:01
    2018년 6월 13일 수요일 오후 12:59
  • Are you doing it inside transaction? 

    It may be that there was some error in the expression causing value to become NULL


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 6월 13일 수요일 오후 2:29
  • Yes, 

    I am using following code to generate the Tran ID;

    if (columnName == "tran_id")
                {
                    sqlCmd = new SqlCommand();
                    sqlCmd.Connection = dbConnection.GetConnection(); ;
                    sqlCmd.CommandType = CommandType.Text;
                    //sqlCmd.CommandText = "select max(SUBSTRING(" + columnName + ",4,4)) from " + tblName;
                    sqlCmd.CommandText = "select max(cast(SUBSTRING(" + columnName + ",4,5)as numeric(18,0))) from " + tblName;
                    int id = Convert.ToInt16(sqlCmd.ExecuteScalar());
                    id = id + 1;
                    if (id != null)
                    {
                        if (id <= 10 && id > 0)
                        {
                            GenID = "IVR00" + Convert.ToString(id);
    
                        }
                        else if (id <= 100 && id > 10)
                        {
                            GenID = "IVR0" + Convert.ToString(id);
    
                        }
                        else if (id <= 1000 && id > 100)
                        {
                            GenID = "IVR" + Convert.ToString(id);
    
                        }
                        else if (id <= 10000 && id > 1000)
                        {
                            GenID = "IVR" + Convert.ToString(id);
    
                        }
                        else if (id <= 100000 && id > 10000)
                        {
                            GenID = "IVR" + Convert.ToString(id);
    
                        }
                        else if (id <= 1000000 && id > 100000)
                        {
                            GenID = "IVR" + Convert.ToString(id);
    
                        }
                    }
                    else
                        //MessageBox.Show("Id generation has failed!");
                        sqlCmd.Dispose();
                }

    I don't know why suddenly null value passed...

    Thanks & Regards,

    2018년 6월 13일 수요일 오후 6:03
  • Try this:

     int id = Convert.ToInt32(sqlCmd.ExecuteScalar());

    2018년 6월 13일 수요일 오후 6:13
    중재자
  • Try this:

     int id = Convert.ToInt32(sqlCmd.ExecuteScalar());

    Thanks Sir..I will try...
    2018년 6월 14일 목요일 오전 1:47
  • Arithmetic overflow error converting expression to data type varchar.

    At the beginning of this topic you mentioned the existence of 1,200 rows. but there are probably more than 9,999 rows in the tbl_ivtransaction table. What is the current number of rows in the tbl_ivtransaction table?

    The suggested code was to be run once, to update the contents of the tran_id column. It is not a code to run on a daily basis. Was there any reason to run it again?


    e-mail       José Diz     Belo Horizonte, MG - Brasil



    Please reply me to fix the query.

    Thanks

    2018년 6월 14일 목요일 오전 1:47
  • Now it is crossing 12000 Rows and length of tran id is 8 digit now ie. "IVR12130"

    Try

    -- code #1 v5
    with Sequence as (
    SELECT *, Seq= row_number() over (order by date_of_entry)
      from tbl_ivtransaction
    )
    UPDATE Sequence
      set tran_id= 'IVR' + 
                  case when Seq <= 999
                            then Right('00' + Cast(Seq as varchar(3)), 3)
    else Cast(Seq as varchar(10)) end;


    e-mail       José Diz     Belo Horizonte, MG - Brasil




    • 편집됨 José Diz 2018년 6월 14일 목요일 오전 11:14
    • 답변으로 제안됨 José Diz 2018년 6월 14일 목요일 오후 3:12
    2018년 6월 14일 목요일 오전 10:08
  • Now it is crossing 12000 Rows and length of tran id is 8 digit now ie. "IVR12130"

    Try

    -- code #1 v5
    with Sequence as (
    SELECT *, Seq= row_number() over (order by date_of_entry)
      from tbl_ivtransaction
    )
    UPDATE Sequence
      set tran_id= 'IVR' + 
                  case when Seq <= 999
                            then Right('00' + Cast(Seq as varchar(3)), 3)
    else Cast(Seq as varchar(10)) end;


    e-mail       José Diz     Belo Horizonte, MG - Brasil




    Thank you very much..
    2018년 6월 14일 목요일 오후 1:21