Create different tables with values from another

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create different tables with values from another

Author

Topic

meinecelina
Starting Member

2 Posts

Posted - 2011-08-25 : 03:31:22

Hi,

I hope you can help me (unfortunately I am a newbie in SQL)

I have two tables.
In table1 I have the data, that I am interested in. There are two columns (speciality1 and speciality2). Here I have 25 different values.
Table 2 has 25 rows with 25 speciality values (my "search values" for later)
I need a table per speciality (the value could occur in one of the two colums) and want to create these new tables all at once.

I would be really gratefull for your help and any hints.

Thanks in advance
Celina

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-25 : 03:36:44

quote:
In table1 I have the data, that I am interested in. There are two columns (speciality1 and speciality2). Here I have 25 different values.
Table 2 has 25 rows with 25 speciality values (my "search values" for later)
I need a table per speciality

That sounds like a very bad idea. Why do you want to do this ? If you have 10,000 speciality value, does that mean you will also 10,000 tables ?

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 03:47:23

Can you have a single "lookup table" instead?

Column1 = Specialty
Column2, 3, ... are the columns you were planning to put into your "one table per speciality"

Your primary key will be Column1 + whichever other column(s) you had planned to use in your "one table per speciality"

If that's not the answer we need to understand the problem you are trying to solve more clearly in order to make some suggestions.

one-table-per-ANYTHING is generally a bad idea ... but never-say-never

meinecelina
Starting Member

2 Posts

Posted - 2011-08-25 : 04:24:40

Thank your for the answers so far.

I am sorry, I wrote it down in a hurry and as I said I am a newbie

Okay, more about the context:

Each week I get a table with 70000 rows from a medical database.
Each entry (row) is associated to two (out of twenty-five possible values), so I have to ‘split’ the table into 25 parts….
What would be a better idea to handle this. Any hint and help welcome.

Thank you again for your help.

Kristen
Test

22859 Posts

Posted - 2011-08-25 : 04:51:42

Does this help?


SELECT T.Column1, T.Column2, ..., S1.Col1, S1.Col2, ..., S2.Col1, S2.Col2, ...
FROM MyTable AS T
     LEFT OUTER JOIN SpecialityTable AS S1
        ON S1.MyKey = T.Speciality1
     LEFT OUTER JOIN SpecialityTable AS S2
        ON S2.MyKey = T.Speciality2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources