Post reply

Split Table

  • July 21, 2003 at 3:58 am

    #82017

    Is there a way to split a table automaticaly like in Ms Access ?

    For Example I have a table with a "name", "address" and "Category" columns.

    Values in "category" are very often the same.

    I want to create a new table with "name", "address" and "categoy_id" related to a new "category table" with "categoy_id" and "categoy_name" where duplicated "category_name" values are eliminated.

    Thanks a lot for your help

  • July 21, 2003 at 7:43 am

    #466204

    You could create a DTS package to do this or you could write a script. Either way, there is no automatic table split.


    Joseph

  • July 22, 2003 at 7:33 am

    #466391

    Have you considered importing it into Access, splitting the tables, and bringing it back into SQL Server?

  • July 22, 2003 at 7:50 am

    #466400

    quote:

    Have you considered importing it into Access, splitting the tables, and bringing it back into SQL Server?

    Yes but the amount of records is absolutely huge and MsAccess can't take it !

  • July 22, 2003 at 7:56 am

    #466404

    Hi jl75@9online.fr,

    quote:

    Yes but the amount of records is absolutely huge and MsAccess can't take it !

    If that is an alternative to go, maybe you can split this amount like first quartile, second, ...?

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • July 22, 2003 at 9:22 am

    #466424

    Here are the steps I would suggest: (I called my "Big Table" Contacts and my category table "Category".)

    1. Review the original categories for any misspellings that might cause there to be 2 or more of the same category to appear. In other words, clean up the data before you begin.

    2. Create a table called "Category" with an identity field (CatID) and a field for the category names (category).

    3. Write a insert statement:

    INSERT INTO Category (Category)

    SELECT DISTINCT(Category) FROM Contacts

    This will make sure there is only one distinct entry for a category in that table.

    3. Add another column to your original table with a datatype of int.

    4. Now you need to match up each row in your original table with the associated catID in the new category table:

    UPDATE Contacts

    SET CatID = Cat.CatID

    FROM Category Cat, Contacts Con

    WHERE Cat.Category = Con.Category

    5. And now you can drop the category column in the old table.

    What this does is it sets up the Category table so each category has an unique ID number associated with it and only it. Then you join the two tables when you want to reference the category at a later date.

    I tested this on my development system and it worked fine for me.

    Thanks!

    ~Tempest

    Edited by - tempest_skye on 07/22/2003 09:24:55 AM

    Edited by - tempest_skye on 07/22/2003 09:27:34 AM

  • July 23, 2003 at 5:52 am

    #466564

    Hi tempest_skye,

    Thanks a lot for your help. It works just fine !

    Cheers

    JL

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply