Tags: convert, datatipe, field, guid, historical, int, integer, newid, programming, records, safe, sql, ticket_number, unique

Convert int to newid (GUID)

On Programmer » SQL

8,256 words with 7 Comments; publish: Wed, 28 May 2008 23:26:00 GMT; (200109.38, « »)

I have a unique field ticket_number(int).

I have historical data in this field (46000 records).

Is there any safe way to change integer(4) datatipe to newid and not affect

historical data.

thanks much

All Comments

Leave a comment...

  • 7 Comments
    • "agenda9533" <agenda9533.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      news:D59BFDBA-A901-462E-9D22-73A471072013.sql.todaysummary.com.microsoft.com...

      >I have a unique field ticket_number(int).

      > I have historical data in this field (46000 records).

      > Is there any safe way to change integer(4) datatipe to newid and not

      > affect

      > historical data.

      > thanks much

      -- Rename the old column and add a new one:

      EXEC sp_rename 'dbo.tbl.ticket_number', 'ticket_number1', 'column';

      ALTER TABLE tbl ADD ticket_number UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL;

      -- Repeat for any occurences of the same column in other tables

      -- Now update any references to the old ticket_number

      UPDATE tbl2 SET ticket_number = tbl.ticket_number

      FROM tbl

      WHERE tbl2.ticket_number1 = tbl.ticket_number1;

      -- Now drop any constraints / indexes on ticket_number1 and add them to

      ticket_number

      -- Then drop the old column(s)

      ALTER TABLE tbl DROP COLUMN ticket_number1;

      Take a backup before you attempt any of this... :-)

      --

      David Portas

      #1; Wed, 28 May 2008 23:27:00 GMT
    • Thank you David!

      I'm a bit confused - what do I need tbl2 table for?

      Is it a copy of tbl ?

      "David Portas" wrote:

      > "agenda9533" <agenda9533.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:D59BFDBA-A901-462E-9D22-73A471072013.sql.todaysummary.com.microsoft.com...

      > >I have a unique field ticket_number(int).

      > > I have historical data in this field (46000 records).

      > > Is there any safe way to change integer(4) datatipe to newid and not

      > > affect

      > > historical data.

      > >

      > > thanks much

      > -- Rename the old column and add a new one:

      > EXEC sp_rename 'dbo.tbl.ticket_number', 'ticket_number1', 'column';

      > ALTER TABLE tbl ADD ticket_number UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL;

      > -- Repeat for any occurences of the same column in other tables

      > -- Now update any references to the old ticket_number

      > UPDATE tbl2 SET ticket_number = tbl.ticket_number

      > FROM tbl

      > WHERE tbl2.ticket_number1 = tbl.ticket_number1;

      > -- Now drop any constraints / indexes on ticket_number1 and add them to

      > ticket_number

      > -- Then drop the old column(s)

      > ALTER TABLE tbl DROP COLUMN ticket_number1;

      > Take a backup before you attempt any of this... :-)

      > --

      > David Portas

      >

      >

      #2; Wed, 28 May 2008 23:28:00 GMT
    • Thank you David!

      I'm a bit confused - what is the purpose of tbl2 table?

      I understood that tbl remans to be actual table. Is tbl2 just a copy of

      tbl?...

      "David Portas" wrote:

      > "agenda9533" <agenda9533.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:D59BFDBA-A901-462E-9D22-73A471072013.sql.todaysummary.com.microsoft.com...

      > >I have a unique field ticket_number(int).

      > > I have historical data in this field (46000 records).

      > > Is there any safe way to change integer(4) datatipe to newid and not

      > > affect

      > > historical data.

      > >

      > > thanks much

      > -- Rename the old column and add a new one:

      > EXEC sp_rename 'dbo.tbl.ticket_number', 'ticket_number1', 'column';

      > ALTER TABLE tbl ADD ticket_number UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL;

      > -- Repeat for any occurences of the same column in other tables

      > -- Now update any references to the old ticket_number

      > UPDATE tbl2 SET ticket_number = tbl.ticket_number

      > FROM tbl

      > WHERE tbl2.ticket_number1 = tbl.ticket_number1;

      > -- Now drop any constraints / indexes on ticket_number1 and add them to

      > ticket_number

      > -- Then drop the old column(s)

      > ALTER TABLE tbl DROP COLUMN ticket_number1;

      > Take a backup before you attempt any of this... :-)

      > --

      > David Portas

      >

      >

      #3; Wed, 28 May 2008 23:29:00 GMT
    • So there is no way to preserve old ticket numbers.

      All 46000 OLD ticket numbers will be replaced by newly generated newid-s?

      "David Portas" wrote:

      > "agenda9533" <agenda9533.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:D59BFDBA-A901-462E-9D22-73A471072013.sql.todaysummary.com.microsoft.com...

      > >I have a unique field ticket_number(int).

      > > I have historical data in this field (46000 records).

      > > Is there any safe way to change integer(4) datatipe to newid and not

      > > affect

      > > historical data.

      > >

      > > thanks much

      > -- Rename the old column and add a new one:

      > EXEC sp_rename 'dbo.tbl.ticket_number', 'ticket_number1', 'column';

      > ALTER TABLE tbl ADD ticket_number UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL;

      > -- Repeat for any occurences of the same column in other tables

      > -- Now update any references to the old ticket_number

      > UPDATE tbl2 SET ticket_number = tbl.ticket_number

      > FROM tbl

      > WHERE tbl2.ticket_number1 = tbl.ticket_number1;

      > -- Now drop any constraints / indexes on ticket_number1 and add them to

      > ticket_number

      > -- Then drop the old column(s)

      > ALTER TABLE tbl DROP COLUMN ticket_number1;

      > Take a backup before you attempt any of this... :-)

      > --

      > David Portas

      >

      >

      #4; Wed, 28 May 2008 23:30:00 GMT
    • "agenda9533" <agenda9533.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      news:E11C5367-02CA-459E-8CBB-90886FCF57EB.sql.todaysummary.com.microsoft.com...

      >

      Tbl2 was supposed to represent any related table that referenced Tbl using a

      foreign key. If there is no such table then ignore that bit.

      > So there is no way to preserve old ticket numbers.

      > All 46000 OLD ticket numbers will be replaced by newly generated newid-s?

      Either you want the old ticket numbers or you want the new UNIQUEIDENTIFIERs

      (at least that's what I assumed you meant when you said "newid" - maybe I

      was wrong). Or perhaps you wanted both, in which case you just need two

      columns. I'm not sure what your question is.

      --

      David Portas

      #5; Wed, 28 May 2008 23:31:00 GMT
    • On Oct 18, 7:44 am, "David Portas"

      <REMOVE_BEFORE_REPLYING_dpor....sql.todaysummary.com.acm.org> wrote:

      > "agenda9533" <agenda9....sql.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:E11C5367-02CA-459E-8CBB-90886FCF57EB.sql.todaysummary.com.microsoft.com...

      >

      > Tbl2 was supposed to represent any related table that referenced Tbl using a

      > foreign key. If there is no such table then ignore that bit.

      > > So there is no way to preserve old ticket numbers.

      > > All 46000 OLD ticket numbers will be replaced by newly generated newid-s?

      > Either you want the old ticket numbers or you want the new UNIQUEIDENTIFIERs

      > (at least that's what I assumed you meant when you said "newid" - maybe I

      > was wrong). Or perhaps you wanted both, in which case you just need two

      > columns. I'm not sure what your question is.

      > --

      > David Portas

      He wants to keep the numbers, but have them in the datatype NewID.

      #6; Wed, 28 May 2008 23:32:00 GMT
    • I wanted to keep the old numbers, but have them in the datatype NewID.

      Is it doable?

      Thank you!

      "David Portas" wrote:

      > "agenda9533" <agenda9533.sql.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:E11C5367-02CA-459E-8CBB-90886FCF57EB.sql.todaysummary.com.microsoft.com...

      > >

      > Tbl2 was supposed to represent any related table that referenced Tbl using a

      > foreign key. If there is no such table then ignore that bit.

      > > So there is no way to preserve old ticket numbers.

      > > All 46000 OLD ticket numbers will be replaced by newly generated newid-s?

      > Either you want the old ticket numbers or you want the new UNIQUEIDENTIFIERs

      > (at least that's what I assumed you meant when you said "newid" - maybe I

      > was wrong). Or perhaps you wanted both, in which case you just need two

      > columns. I'm not sure what your question is.

      > --

      > David Portas

      >

      >

      #7; Wed, 28 May 2008 23:33:00 GMT