Loading ...
Sorry, an error occurred while loading the content.

Re: MUGH-SQLCON Enabling Identity Property

Expand Messages
  • smart chip
    Sorry I written the question wrongly in sql 200 identity columnds not apdatable?. I mean by this is not updatable. any one succeded by doing this? I think the
    Message 1 of 12 , Dec 1, 2006
    • 0 Attachment
      Sorry I written the question wrongly in sql 200 identity columnds not apdatable?. I mean by this is not updatable.

      any one succeded by doing this? I think the followng is not possible (which I thought by copying mena) I know that it will create a new tables

      Set identity_insert Testtable ON
      Update testtable set IDColumn = somevalue
      set identity_insert Testtable OFF


      Regards,
      K. Kalyan

      Babu PP <babupp@...> wrote:
      What is really menat by 3."copy the existsing value to th new column"?

      My existing table is with an INT key field which is incremented manually, by
      finding the max and incrementing by one, each time a new row is inserted.
      Now I want to make the Id column an Identity field, retaining the existing
      data. I can neither lose the existing data nor change the Id of rows. In
      this situation we can set the IDENTITY_INSERT on and insert values, set
      IDENTITY_INSERT off and RESEED the column for normal operation.

      As per the suggestion in the first mail, I can add new column, say NewId,
      with Identity property On, copy the values to NewId column, drop Id column
      and rename NewId as Id. The draw back with this method is that the newly
      added column will be at the end.

      My thinking is if we want the existing values to be populated in new column
      th only way is to create new table.

      It is not necessarily to be like that always, but if you need the identity
      column as first column. It is possible to add a new column with Identity
      property on.

      In 2000 identity columns are not updatable? or is it enhancement in 2005?

      It was never told that the identity columns are not updatable, but on an
      existing column you cannot enable the identity property.

      Option is given in Management Studio, in Modify table, to set Identity as
      'Yes'. But if you trace the real operation, you can find that it is
      creating a new table with Identity property, copying the existing data to
      new table, dropping the original one, and renaming the new table to the
      original name.

      Regards

      Babu

      On 12/1/06, smart chip <knowledge_chip@... > wrote:
      >
      > I am intrested to know one thing..
      >
      > What is really menat by 3."copy the existsing value to th new column"?
      >
      > My thinking is if we want the existing values to be populated in new
      > column th only way is to create new table.
      >
      > In 2000 identity columns are not updatable? or is it enhancement in 2005?
      >
      > Roji P Thomas <thomasroji@... <thomasroji%40hotmail.com>> wrote:
      > Well, Technically No.
      > What you can do is
      >
      > 1. Create a new field as identity
      > 2. Set identity insert on
      > 3. Copy the exising value to the new column
      > 4. set identity insert off
      > 5. drop the existing column
      > 6. rename the new column with the old column name.
      >
      > Regards
      > Roji. P. Thomas
      > http://toponewithties.blogspot.com
      > ----- Original Message -----
      > From: Babu PP
      > To: sqlcon@yahoogroups.com <sqlcon%40yahoogroups.com>
      > Sent: Tuesday, November 28, 2006 11:25 AM
      > Subject: MUGH-SQLCON Enabling Identity Property
      >
      > Hi,
      >
      > Is it possible to enable identity property of a column (Integer data) in a
      > table with some amount of data, using SQL Scripts ?
      >
      > Thanks in adance.
      >
      > Regards
      >
      > Babu
      >
      > [Non-text portions of this message have been removed]
      >
      > [Non-text portions of this message have been removed]
      >
      > ---------------------------------
      > Everyone is raving about the all-new Yahoo! Mail beta.
      >
      > [Non-text portions of this message have been removed]
      >
      >
      >

      [Non-text portions of this message have been removed]






      ---------------------------------
      Everyone is raving about the all-new Yahoo! Mail beta.

      [Non-text portions of this message have been removed]
    • Santhosh
      Is it possible to enable identity property of a column (Integer data) in a table with some amount of data, using SQL Scripts ? We cannot do this by script but
      Message 2 of 12 , Dec 3, 2006
      • 0 Attachment
        Is it possible to enable identity property of a column (Integer data) in a

        table with some amount of data, using SQL Scripts ?

        We cannot do this by script but we can do this using enterprise manager. Even if you set the initial values as 1, the SQL Server is smart enough to find out the maximum value of that column and will start inserting from next value when u do the next insert.

        If you want to do this using the script, you need to create a new table with similar structure and the transfer the data by using the below statements

        SET IDENTITY_INSERT test1 ON
        insert into test1(test,test1) select test,test1 from test
        SET IDENTITY_INSERT test1 OFF

        Make sure that you set the identity_insert off on the table. This is the new thing I found out today like you can set the identity_insert on only one table in a database at any point of time :).

        With Smiles
        Santhosh S.J.
        Failure Means Delay Not Defeat
        ReadMe @ sqlspy.blogspot.com

        ----- Original Message ----
        From: smart chip <knowledge_chip@...>
        To: sqlcon@yahoogroups.com
        Sent: Friday, December 1, 2006 10:57:09 PM
        Subject: Re: MUGH-SQLCON Enabling Identity Property













        Sorry I written the question wrongly in sql 200 identity columnds not apdatable?. I mean by this is not updatable.



        any one succeded by doing this? I think the followng is not possible (which I thought by copying mena) I know that it will create a new tables



        Set identity_insert Testtable ON

        Update testtable set IDColumn = somevalue

        set identity_insert Testtable OFF





        Regards,

        K. Kalyan



        Babu PP <babupp@gmail. com> wrote:

        What is really menat by 3."copy the existsing value to th new column"?



        My existing table is with an INT key field which is incremented manually, by

        finding the max and incrementing by one, each time a new row is inserted.

        Now I want to make the Id column an Identity field, retaining the existing

        data. I can neither lose the existing data nor change the Id of rows. In

        this situation we can set the IDENTITY_INSERT on and insert values, set

        IDENTITY_INSERT off and RESEED the column for normal operation.



        As per the suggestion in the first mail, I can add new column, say NewId,

        with Identity property On, copy the values to NewId column, drop Id column

        and rename NewId as Id. The draw back with this method is that the newly

        added column will be at the end.



        My thinking is if we want the existing values to be populated in new column

        th only way is to create new table.



        It is not necessarily to be like that always, but if you need the identity

        column as first column. It is possible to add a new column with Identity

        property on.



        In 2000 identity columns are not updatable? or is it enhancement in 2005?



        It was never told that the identity columns are not updatable, but on an

        existing column you cannot enable the identity property.



        Option is given in Management Studio, in Modify table, to set Identity as

        'Yes'. But if you trace the real operation, you can find that it is

        creating a new table with Identity property, copying the existing data to

        new table, dropping the original one, and renaming the new table to the

        original name.



        Regards



        Babu



        On 12/1/06, smart chip <knowledge_chip@ yahoo.com > wrote:

        >

        > I am intrested to know one thing..

        >

        > What is really menat by 3."copy the existsing value to th new column"?

        >

        > My thinking is if we want the existing values to be populated in new

        > column th only way is to create new table.

        >

        > In 2000 identity columns are not updatable? or is it enhancement in 2005?

        >

        > Roji P Thomas <thomasroji@hotmail. com <thomasroji% 40hotmail. com>> wrote:

        > Well, Technically No.

        > What you can do is

        >

        > 1. Create a new field as identity

        > 2. Set identity insert on

        > 3. Copy the exising value to the new column

        > 4. set identity insert off

        > 5. drop the existing column

        > 6. rename the new column with the old column name.

        >

        > Regards

        > Roji. P. Thomas

        > http://toponewithti es.blogspot. com

        > ----- Original Message -----

        > From: Babu PP

        > To: sqlcon@yahoogroups. com <sqlcon%40yahoogrou ps.com>

        > Sent: Tuesday, November 28, 2006 11:25 AM

        > Subject: MUGH-SQLCON Enabling Identity Property

        >

        > Hi,

        >

        > Is it possible to enable identity property of a column (Integer data) in a

        > table with some amount of data, using SQL Scripts ?

        >

        > Thanks in adance.

        >

        > Regards

        >

        > Babu

        >

        > [Non-text portions of this message have been removed]

        >

        > [Non-text portions of this message have been removed]

        >

        > ------------ --------- --------- ---

        > Everyone is raving about the all-new Yahoo! Mail beta.

        >

        > [Non-text portions of this message have been removed]

        >

        >

        >



        [Non-text portions of this message have been removed]



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

        Everyone is raving about the all-new Yahoo! Mail beta.



        [Non-text portions of this message have been removed]














        <!--

        #ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
        #ygrp-mlmsg table {font-size:inherit;font:100%;}
        #ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
        #ygrp-mlmsg pre, code {font:115% monospace;}
        #ygrp-mlmsg * {line-height:1.22em;}
        #ygrp-text{
        font-family:Georgia;
        }
        #ygrp-text p{
        margin:0 0 1em 0;
        }
        #ygrp-tpmsgs{
        font-family:Arial;
        clear:both;
        }
        #ygrp-vitnav{
        padding-top:10px;
        font-family:Verdana;
        font-size:77%;
        margin:0;
        }
        #ygrp-vitnav a{
        padding:0 1px;
        }
        #ygrp-actbar{
        clear:both;
        margin:25px 0;
        white-space:nowrap;
        color:#666;
        text-align:right;
        }
        #ygrp-actbar .left{
        float:left;
        white-space:nowrap;
        }
        .bld{font-weight:bold;}
        #ygrp-grft{
        font-family:Verdana;
        font-size:77%;
        padding:15px 0;
        }
        #ygrp-ft{
        font-family:verdana;
        font-size:77%;
        border-top:1px solid #666;
        padding:5px 0;
        }
        #ygrp-mlmsg #logo{
        padding-bottom:10px;
        }

        #ygrp-vital{
        background-color:#e0ecee;
        margin-bottom:20px;
        padding:2px 0 8px 8px;
        }
        #ygrp-vital #vithd{
        font-size:77%;
        font-family:Verdana;
        font-weight:bold;
        color:#333;
        text-transform:uppercase;
        }
        #ygrp-vital ul{
        padding:0;
        margin:2px 0;
        }
        #ygrp-vital ul li{
        list-style-type:none;
        clear:both;
        border:1px solid #e0ecee;
        }
        #ygrp-vital ul li .ct{
        font-weight:bold;
        color:#ff7900;
        float:right;
        width:2em;
        text-align:right;
        padding-right:.5em;
        }
        #ygrp-vital ul li .cat{
        font-weight:bold;
        }
        #ygrp-vital a {
        text-decoration:none;
        }

        #ygrp-vital a:hover{
        text-decoration:underline;
        }

        #ygrp-sponsor #hd{
        color:#999;
        font-size:77%;
        }
        #ygrp-sponsor #ov{
        padding:6px 13px;
        background-color:#e0ecee;
        margin-bottom:20px;
        }
        #ygrp-sponsor #ov ul{
        padding:0 0 0 8px;
        margin:0;
        }
        #ygrp-sponsor #ov li{
        list-style-type:square;
        padding:6px 0;
        font-size:77%;
        }
        #ygrp-sponsor #ov li a{
        text-decoration:none;
        font-size:130%;
        }
        #ygrp-sponsor #nc {
        background-color:#eee;
        margin-bottom:20px;
        padding:0 8px;
        }
        #ygrp-sponsor .ad{
        padding:8px 0;
        }
        #ygrp-sponsor .ad #hd1{
        font-family:Arial;
        font-weight:bold;
        color:#628c2a;
        font-size:100%;
        line-height:122%;
        }
        #ygrp-sponsor .ad a{
        text-decoration:none;
        }
        #ygrp-sponsor .ad a:hover{
        text-decoration:underline;
        }
        #ygrp-sponsor .ad p{
        margin:0;
        }
        o {font-size:0;}
        .MsoNormal {
        margin:0 0 0 0;
        }
        #ygrp-text tt{
        font-size:120%;
        }
        blockquote{margin:0 0 0 4px;}
        .replbq {margin:4;}
        -->









        ____________________________________________________________________________________
        Do you Yahoo!?
        Everyone is raving about the all-new Yahoo! Mail beta.
        http://new.mail.yahoo.com

        [Non-text portions of this message have been removed]
      • Roji. P. Thomas
        Babu, Let me correct myself. You cannot UPDATE the value of an IDENTITY column even with the IDENTITY_INSERT Setting ON. So what you have to do is 1. Copy all
        Message 3 of 12 , Dec 3, 2006
        • 0 Attachment
          Babu,

          Let me correct myself. You cannot UPDATE the value of an IDENTITY column even with the IDENTITY_INSERT Setting ON.

          So what you have to do is

          1. Copy all the rows to a temporary table
          2. Truncate the table
          3. drop the existing column
          3. Create a new field as identity with the old column name.
          4. Set identity insert on
          5. Copy all the rows from the temorary table to the new table
          6. set identity insert off



          ----- Original Message -----
          From: Babu PP
          To: sqlcon@yahoogroups.com
          Sent: Friday, December 01, 2006 11:15 AM
          Subject: Re: MUGH-SQLCON Enabling Identity Property


          What is really menat by 3."copy the existsing value to th new column"?

          My existing table is with an INT key field which is incremented manually, by
          finding the max and incrementing by one, each time a new row is inserted.
          Now I want to make the Id column an Identity field, retaining the existing
          data. I can neither lose the existing data nor change the Id of rows. In
          this situation we can set the IDENTITY_INSERT on and insert values, set
          IDENTITY_INSERT off and RESEED the column for normal operation.

          As per the suggestion in the first mail, I can add new column, say NewId,
          with Identity property On, copy the values to NewId column, drop Id column
          and rename NewId as Id. The draw back with this method is that the newly
          added column will be at the end.

          My thinking is if we want the existing values to be populated in new column
          th only way is to create new table.

          It is not necessarily to be like that always, but if you need the identity
          column as first column. It is possible to add a new column with Identity
          property on.

          In 2000 identity columns are not updatable? or is it enhancement in 2005?

          It was never told that the identity columns are not updatable, but on an
          existing column you cannot enable the identity property.

          Option is given in Management Studio, in Modify table, to set Identity as
          'Yes'. But if you trace the real operation, you can find that it is
          creating a new table with Identity property, copying the existing data to
          new table, dropping the original one, and renaming the new table to the
          original name.

          Regards

          Babu

          On 12/1/06, smart chip <knowledge_chip@... > wrote:
          >
          > I am intrested to know one thing..
          >
          > What is really menat by 3."copy the existsing value to th new column"?
          >
          > My thinking is if we want the existing values to be populated in new
          > column th only way is to create new table.
          >
          > In 2000 identity columns are not updatable? or is it enhancement in 2005?
          >
          > Roji P Thomas <thomasroji@... <thomasroji%40hotmail.com>> wrote:
          > Well, Technically No.
          > What you can do is
          >
          > 1. Create a new field as identity
          > 2. Set identity insert on
          > 3. Copy the exising value to the new column
          > 4. set identity insert off
          > 5. drop the existing column
          > 6. rename the new column with the old column name.
          >
          > Regards
          > Roji. P. Thomas
          > http://toponewithties.blogspot.com
          > ----- Original Message -----
          > From: Babu PP
          > To: sqlcon@yahoogroups.com <sqlcon%40yahoogroups.com>
          > Sent: Tuesday, November 28, 2006 11:25 AM
          > Subject: MUGH-SQLCON Enabling Identity Property
          >
          > Hi,
          >
          > Is it possible to enable identity property of a column (Integer data) in a
          > table with some amount of data, using SQL Scripts ?
          >
          > Thanks in adance.
          >
          > Regards
          >
          > Babu
          >
          > [Non-text portions of this message have been removed]
          >
          > [Non-text portions of this message have been removed]
          >
          > ---------------------------------
          > Everyone is raving about the all-new Yahoo! Mail beta.
          >
          > [Non-text portions of this message have been removed]
          >
          >
          >

          [Non-text portions of this message have been removed]





          [Non-text portions of this message have been removed]
        • smart chip
          Hi Spy, I know all these, but why i am confused is, in the original answer Roji suggested to add a new identity column to the existing and copy the data to the
          Message 4 of 12 , Dec 4, 2006
          • 0 Attachment
            Hi Spy,

            I know all these,

            but why i am confused is, in the original answer Roji suggested to add a new identity column to the existing and copy the data to the new column, which is not possible through script with out creating a new table. So I just wanted to know what is the way to do that. If we add it in enterpize manager tis same as creating new table. I thought if it is a enhancement in 2005.



            Santhosh <san_spy@...> wrote:


            Is it possible to enable identity property of a column (Integer data) in a

            table with some amount of data, using SQL Scripts ?

            We cannot do this by script but we can do this using enterprise manager. Even if you set the initial values as 1, the SQL Server is smart enough to find out the maximum value of that column and will start inserting from next value when u do the next insert.

            If you want to do this using the script, you need to create a new table with similar structure and the transfer the data by using the below statements

            SET IDENTITY_INSERT test1 ON
            insert into test1(test,test1) select test,test1 from test
            SET IDENTITY_INSERT test1 OFF

            Make sure that you set the identity_insert off on the table. This is the new thing I found out today like you can set the identity_insert on only one table in a database at any point of time :).

            With Smiles
            Santhosh S.J.
            Failure Means Delay Not Defeat
            ReadMe @ sqlspy.blogspot.com

            ----- Original Message ----
            From: smart chip <knowledge_chip@...>
            To: sqlcon@yahoogroups.com
            Sent: Friday, December 1, 2006 10:57:09 PM
            Subject: Re: MUGH-SQLCON Enabling Identity Property

            Sorry I written the question wrongly in sql 200 identity columnds not apdatable?. I mean by this is not updatable.

            any one succeded by doing this? I think the followng is not possible (which I thought by copying mena) I know that it will create a new tables

            Set identity_insert Testtable ON

            Update testtable set IDColumn = somevalue

            set identity_insert Testtable OFF

            Regards,

            K. Kalyan

            Babu PP <babupp@gmail. com> wrote:

            What is really menat by 3."copy the existsing value to th new column"?

            My existing table is with an INT key field which is incremented manually, by

            finding the max and incrementing by one, each time a new row is inserted.

            Now I want to make the Id column an Identity field, retaining the existing

            data. I can neither lose the existing data nor change the Id of rows. In

            this situation we can set the IDENTITY_INSERT on and insert values, set

            IDENTITY_INSERT off and RESEED the column for normal operation.

            As per the suggestion in the first mail, I can add new column, say NewId,

            with Identity property On, copy the values to NewId column, drop Id column

            and rename NewId as Id. The draw back with this method is that the newly

            added column will be at the end.

            My thinking is if we want the existing values to be populated in new column

            th only way is to create new table.

            It is not necessarily to be like that always, but if you need the identity

            column as first column. It is possible to add a new column with Identity

            property on.

            In 2000 identity columns are not updatable? or is it enhancement in 2005?

            It was never told that the identity columns are not updatable, but on an

            existing column you cannot enable the identity property.

            Option is given in Management Studio, in Modify table, to set Identity as

            'Yes'. But if you trace the real operation, you can find that it is

            creating a new table with Identity property, copying the existing data to

            new table, dropping the original one, and renaming the new table to the

            original name.

            Regards

            Babu

            On 12/1/06, smart chip <knowledge_chip@ yahoo.com > wrote:

            >

            > I am intrested to know one thing..

            >

            > What is really menat by 3."copy the existsing value to th new column"?

            >

            > My thinking is if we want the existing values to be populated in new

            > column th only way is to create new table.

            >

            > In 2000 identity columns are not updatable? or is it enhancement in 2005?

            >

            > Roji P Thomas <thomasroji@hotmail. com <thomasroji% 40hotmail. com>> wrote:

            > Well, Technically No.

            > What you can do is

            >

            > 1. Create a new field as identity

            > 2. Set identity insert on

            > 3. Copy the exising value to the new column

            > 4. set identity insert off

            > 5. drop the existing column

            > 6. rename the new column with the old column name.

            >

            > Regards

            > Roji. P. Thomas

            > http://toponewithti es.blogspot. com

            > ----- Original Message -----

            > From: Babu PP

            > To: sqlcon@yahoogroups. com <sqlcon%40yahoogrou ps.com>

            > Sent: Tuesday, November 28, 2006 11:25 AM

            > Subject: MUGH-SQLCON Enabling Identity Property

            >

            > Hi,

            >

            > Is it possible to enable identity property of a column (Integer data) in a

            > table with some amount of data, using SQL Scripts ?

            >

            > Thanks in adance.

            >

            > Regards

            >

            > Babu

            >

            > [Non-text portions of this message have been removed]

            >

            > [Non-text portions of this message have been removed]

            >

            > ------------ --------- --------- ---

            > Everyone is raving about the all-new Yahoo! Mail beta.

            >

            > [Non-text portions of this message have been removed]

            >

            >

            >

            [Non-text portions of this message have been removed]

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

            Everyone is raving about the all-new Yahoo! Mail beta.

            [Non-text portions of this message have been removed]

            <!--

            #ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
            #ygrp-mlmsg table {font-size:inherit;font:100%;}
            #ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
            #ygrp-mlmsg pre, code {font:115% monospace;}
            #ygrp-mlmsg * {line-height:1.22em;}
            #ygrp-text{
            font-family:Georgia;
            }
            #ygrp-text p{
            margin:0 0 1em 0;
            }
            #ygrp-tpmsgs{
            font-family:Arial;
            clear:both;
            }
            #ygrp-vitnav{
            padding-top:10px;
            font-family:Verdana;
            font-size:77%;
            margin:0;
            }
            #ygrp-vitnav a{
            padding:0 1px;
            }
            #ygrp-actbar{
            clear:both;
            margin:25px 0;
            white-space:nowrap;
            color:#666;
            text-align:right;
            }
            #ygrp-actbar .left{
            float:left;
            white-space:nowrap;
            }
            .bld{font-weight:bold;}
            #ygrp-grft{
            font-family:Verdana;
            font-size:77%;
            padding:15px 0;
            }
            #ygrp-ft{
            font-family:verdana;
            font-size:77%;
            border-top:1px solid #666;
            padding:5px 0;
            }
            #ygrp-mlmsg #logo{
            padding-bottom:10px;
            }

            #ygrp-vital{
            background-color:#e0ecee;
            margin-bottom:20px;
            padding:2px 0 8px 8px;
            }
            #ygrp-vital #vithd{
            font-size:77%;
            font-family:Verdana;
            font-weight:bold;
            color:#333;
            text-transform:uppercase;
            }
            #ygrp-vital ul{
            padding:0;
            margin:2px 0;
            }
            #ygrp-vital ul li{
            list-style-type:none;
            clear:both;
            border:1px solid #e0ecee;
            }
            #ygrp-vital ul li .ct{
            font-weight:bold;
            color:#ff7900;
            float:right;
            width:2em;
            text-align:right;
            padding-right:.5em;
            }
            #ygrp-vital ul li .cat{
            font-weight:bold;
            }
            #ygrp-vital a {
            text-decoration:none;
            }

            #ygrp-vital a:hover{
            text-decoration:underline;
            }

            #ygrp-sponsor #hd{
            color:#999;
            font-size:77%;
            }
            #ygrp-sponsor #ov{
            padding:6px 13px;
            background-color:#e0ecee;
            margin-bottom:20px;
            }
            #ygrp-sponsor #ov ul{
            padding:0 0 0 8px;
            margin:0;
            }
            #ygrp-sponsor #ov li{
            list-style-type:square;
            padding:6px 0;
            font-size:77%;
            }
            #ygrp-sponsor #ov li a{
            text-decoration:none;
            font-size:130%;
            }
            #ygrp-sponsor #nc {
            background-color:#eee;
            margin-bottom:20px;
            padding:0 8px;
            }
            #ygrp-sponsor .ad{
            padding:8px 0;
            }
            #ygrp-sponsor .ad #hd1{
            font-family:Arial;
            font-weight:bold;
            color:#628c2a;
            font-size:100%;
            line-height:122%;
            }
            #ygrp-sponsor .ad a{
            text-decoration:none;
            }
            #ygrp-sponsor .ad a:hover{
            text-decoration:underline;
            }
            #ygrp-sponsor .ad p{
            margin:0;
            }
            o {font-size:0;}
            .MsoNormal {
            margin:0 0 0 0;
            }
            #ygrp-text tt{
            font-size:120%;
            }
            blockquote{margin:0 0 0 4px;}
            .replbq {margin:4;}
            -->

            __________________________________________________________
            Do you Yahoo!?
            Everyone is raving about the all-new Yahoo! Mail beta.
            http://new.mail.yahoo.com

            [Non-text portions of this message have been removed]






            ---------------------------------
            Everyone is raving about the all-new Yahoo! Mail beta.

            [Non-text portions of this message have been removed]
          • smart chip
            Thanks Roji, sorry i have made so much noice here with typos. I am not good typist. It is also in (My) wish list for 2005. Because we face some problems with
            Message 5 of 12 , Dec 4, 2006
            • 0 Attachment
              Thanks Roji,

              sorry i have made so much noice here with typos. I am not good typist.

              It is also in (My) wish list for 2005.

              Because we face some problems with replication on identities (especially the replciation update commads) they will fail at compilation. we have to edit them, to work correclty.

              I don't know is there any reason why MS not providsing this.



              "Roji. P. Thomas" <thomasroji@...> wrote:
              Babu,

              Let me correct myself. You cannot UPDATE the value of an IDENTITY column even with the IDENTITY_INSERT Setting ON.

              So what you have to do is

              1. Copy all the rows to a temporary table
              2. Truncate the table
              3. drop the existing column
              3. Create a new field as identity with the old column name.
              4. Set identity insert on
              5. Copy all the rows from the temorary table to the new table
              6. set identity insert off

              ----- Original Message -----
              From: Babu PP
              To: sqlcon@yahoogroups.com
              Sent: Friday, December 01, 2006 11:15 AM
              Subject: Re: MUGH-SQLCON Enabling Identity Property

              What is really menat by 3."copy the existsing value to th new column"?

              My existing table is with an INT key field which is incremented manually, by
              finding the max and incrementing by one, each time a new row is inserted.
              Now I want to make the Id column an Identity field, retaining the existing
              data. I can neither lose the existing data nor change the Id of rows. In
              this situation we can set the IDENTITY_INSERT on and insert values, set
              IDENTITY_INSERT off and RESEED the column for normal operation.

              As per the suggestion in the first mail, I can add new column, say NewId,
              with Identity property On, copy the values to NewId column, drop Id column
              and rename NewId as Id. The draw back with this method is that the newly
              added column will be at the end.

              My thinking is if we want the existing values to be populated in new column
              th only way is to create new table.

              It is not necessarily to be like that always, but if you need the identity
              column as first column. It is possible to add a new column with Identity
              property on.

              In 2000 identity columns are not updatable? or is it enhancement in 2005?

              It was never told that the identity columns are not updatable, but on an
              existing column you cannot enable the identity property.

              Option is given in Management Studio, in Modify table, to set Identity as
              'Yes'. But if you trace the real operation, you can find that it is
              creating a new table with Identity property, copying the existing data to
              new table, dropping the original one, and renaming the new table to the
              original name.

              Regards

              Babu

              On 12/1/06, smart chip <knowledge_chip@... > wrote:
              >
              > I am intrested to know one thing..
              >
              > What is really menat by 3."copy the existsing value to th new column"?
              >
              > My thinking is if we want the existing values to be populated in new
              > column th only way is to create new table.
              >
              > In 2000 identity columns are not updatable? or is it enhancement in 2005?
              >
              > Roji P Thomas <thomasroji@... <thomasroji%40hotmail.com>> wrote:
              > Well, Technically No.
              > What you can do is
              >
              > 1. Create a new field as identity
              > 2. Set identity insert on
              > 3. Copy the exising value to the new column
              > 4. set identity insert off
              > 5. drop the existing column
              > 6. rename the new column with the old column name.
              >
              > Regards
              > Roji. P. Thomas
              > http://toponewithties.blogspot.com
              > ----- Original Message -----
              > From: Babu PP
              > To: sqlcon@yahoogroups.com <sqlcon%40yahoogroups.com>
              > Sent: Tuesday, November 28, 2006 11:25 AM
              > Subject: MUGH-SQLCON Enabling Identity Property
              >
              > Hi,
              >
              > Is it possible to enable identity property of a column (Integer data) in a
              > table with some amount of data, using SQL Scripts ?
              >
              > Thanks in adance.
              >
              > Regards
              >
              > Babu
              >
              > [Non-text portions of this message have been removed]
              >
              > [Non-text portions of this message have been removed]
              >
              > ---------------------------------
              > Everyone is raving about the all-new Yahoo! Mail beta.
              >
              > [Non-text portions of this message have been removed]
              >
              >
              >

              [Non-text portions of this message have been removed]

              [Non-text portions of this message have been removed]






              ---------------------------------
              Have a burning question? Go to Yahoo! Answers and get answers from real people who know.

              [Non-text portions of this message have been removed]
            Your message has been successfully submitted and would be delivered to recipients shortly.