Split Table

  • 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

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


    Joseph

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

  • 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 !


  • 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]

  • 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

  • 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