Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server > SQL Server Forums > SQL Server Data Warehousing > changing the table structure

Answered changing the table structure

    •  
      Sunday, February 12, 2012 7:15 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      hi to all,,

      A table has duplicate records ,not having any constraints.

      i want to add all constraints now that table ...how can i change,table is having tera data...

       

    Answers

    • Monday, February 13, 2012 9:03 AM
       
       Answered
      Sign In to Vote
      0
      Sign In to Vote

      let's say your table with duplicates is table1(Column1,Column2).

      create the new table UniqueTable(AutoID, Column1, Column2) , set the column AutoID with the Identity property = true

      then run Insert into UniqueTable(Column1,Column2) (select Column1, column2 from table1). //this will take some time ofcourse

      then you have now uniqness on the uniquetable rename it back to the original one after deletion

      Wagdy Ishac www.sqldair.com

       

    All Replies

    • Sunday, February 12, 2012 1:37 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      One technique would be to create a new table with appropriate unique indexes. Then set the indexes to IGNORE_DUPLICATE_VALUE = TRUE. Now copy the data to the new table with INSERT INTO...SELECT. Next, drop the original table. Now rename the new table to reflect the original name.

      Hope this helps.

      Pete

      Peter Carter http://sqlserverdownanddirty.blogspot.com/

       
    • Monday, February 13, 2012 9:03 AM
       
       Answered
      Sign In to Vote
      0
      Sign In to Vote

      let's say your table with duplicates is table1(Column1,Column2).

      create the new table UniqueTable(AutoID, Column1, Column2) , set the column AutoID with the Identity property = true

      then run Insert into UniqueTable(Column1,Column2) (select Column1, column2 from table1). //this will take some time ofcourse

      then you have now uniqness on the uniquetable rename it back to the original one after deletion

      Wagdy Ishac www.sqldair.com