Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dgaylor
Yak Posting Veteran
54 Posts |
 Posted - 2004-07-20 : 17:59:06
|
| I have a table that has customer entries in it, including a column called TypeCode which can have 50 different values in it (1 to 50). I need to logically assign/split each TypeCode randomly into 3 other specific codes, A, B, and C. So, if I have 100 records with TypeCode 1, then I need to flag 33 of them to be flagges as A, 33 of them as B, and 34 of them as C. If i have 300 records with TypeCode 2, then I need 100 of them flagged as A, 100 as B, and 100 as C.I am basically trying to split this one table, which has 50 different typecodes in it, into 3 smaller tables that have an even distribution of typecode values across the tables.Can anyone help?Thanks. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-07-20 : 19:37:07
|
| Ummm...Okay, I give up. Why are you doing this? Is this just a conceptual test-run of a MUCH larger project? Because certainly having 50 records in one table is not an issue.Even if you had millions of rows in one table, I would seriously question whether it is a good idea to split it up into multiple tables of identical structure. We often get questions about how to then re-combine data when people have done this, or even better, about how to figure out which one of the many tables they have the data they want is in. And then there's the question of Foreign Keys which can get quite ugly. I would seriously reconsider the reason for this type of change before moving forward, and if you'd like to share, perhaps we can provide some constructive suggestions for better design.But for your simple suggestion, assuming this is just a one-off, then I'd use something like an INSER...SELECT statement with the SELECT using a TOP NN in it. Figure out the value of NN up front since it's only a one-off.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-20 : 20:04:22
|
| Here's a fun idea for you. Have a table for the types, and one for the values. The type table needs to have a SuperType column or category column and an active flag. Have a join table between types and values. Each time a new value is inserted, insert an entry to thte join table for the appropriate category with the active flag. Move the active flag to the next item in the category (or the first item if you are at the max). That should be fun to maintain, an absolute blast. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-07-21 : 14:24:28
|
| Ok I can split them into 3 tables(derived) if you like.create view myAs as select top 34 percent * /*ok bad*/ from tablecreate view myBs as select top 33 percent * /*ok bad*/ from tablecreate view myCs as select top 33 percent * /*ok bad*/ from tableEdit..PLS Ignore Momentary Lapse of Reason.Can theoretically be done with a bit work though.../rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-21 : 14:34:38
|
| "Why are you doing this?"Could be to assign Drug A, Drug B and Placebo?Don't see the need to put then in separate tables though, just allocate a TypeCode column randomly to A, B or C.Kristen |
|
|
|
|
|
|
|
|
Need to assign one of 3 values