Breaking up a Table

I hope I can get this across clearly.
I have a table that needs to be broken into 3 tables.
Col1 Col2 Col3 Col4 Col5 Col6 Col7
Col1 and Col2 need to go into LookupTable1
Col3 and Col4 into LookupTable2
If Col5 is twice the width... haha just kidding...
so Col5 and Col6 go into LookupTable3
There is a 4th table which is made up of foreign keys which are the PK of
LookupTable1,2,3
My questions is, how to get the data from the columns of each row and add it
to its respective lookuptable
and sequentially step throw the table to repeat the above step until I've
processed each row
thanks folks
T.BThe Bear wrote:
> I hope I can get this across clearly.
> I have a table that needs to be broken into 3 tables.
> Col1 Col2 Col3 Col4 Col5 Col6 Col7
> Col1 and Col2 need to go into LookupTable1
> Col3 and Col4 into LookupTable2
> If Col5 is twice the width... haha just kidding...
> so Col5 and Col6 go into LookupTable3
> There is a 4th table which is made up of foreign keys which are the
> PK of LookupTable1,2,3
> My questions is, how to get the data from the columns of each row and
> add it to its respective lookuptable
> and sequentially step throw the table to repeat the above step until
> I've processed each row
> thanks folks
> T.B
Since your DDL is a mystery, I'll assume col1 and col2 are an
ID/Description combo:
Insert Into LookupTable1 (
col1,
col2 )
Select DISTINCT Col1, Col2
From Table
etc...
David Gugick
Imceda Software
www.imceda.com|||What's a "lookup table"? No such thing in any relational database.
There is only one kind of table.
I guess the following is what you want. There should be absolutely no
reason to do this sequentially row by row.
INSERT INTO Table1 (col1, col2)
SELECT DISTINCT col1, col2
FROM YourTable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL
INSERT INTO Table2 (col3, col4)
SELECT DISTINCT col3, col4
FROM YourTable
WHERE col3 IS NOT NULL
AND col4 IS NOT NULL
.. etc
INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
FROM YourTable AS T0
JOIN Table1 AS T1
ON T0.col1 = T1.col1
AND T0.col2 = T1.col2
JOIN Table2 AS T2
ON T0.col3 = T2.col3
AND T0.col4 = T2.col4
.. etc
David Portas
SQL Server MVP
--|||Thanks folks....
Lookup Table was a term used to help with the understanding of the question
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114019514.957668.75170@.f14g2000cwb.googlegroups.com...
> What's a "lookup table"? No such thing in any relational database.
> There is only one kind of table.
> I guess the following is what you want. There should be absolutely no
> reason to do this sequentially row by row.
> INSERT INTO Table1 (col1, col2)
> SELECT DISTINCT col1, col2
> FROM YourTable
> WHERE col1 IS NOT NULL
> AND col2 IS NOT NULL
> INSERT INTO Table2 (col3, col4)
> SELECT DISTINCT col3, col4
> FROM YourTable
> WHERE col3 IS NOT NULL
> AND col4 IS NOT NULL
> .. etc
> INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
> SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
> FROM YourTable AS T0
> JOIN Table1 AS T1
> ON T0.col1 = T1.col1
> AND T0.col2 = T1.col2
> JOIN Table2 AS T2
> ON T0.col3 = T2.col3
> AND T0.col4 = T2.col4
> ... etc
> --
> David Portas
> SQL Server MVP
> --
>