469,645 Members | 1,143 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,645 developers. It's quick & easy.

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.B
Jul 23 '05 #1
3 1329
The 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

Jul 23 '05 #2
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
--

Jul 23 '05 #3
Thanks folks.....

Lookup Table was a term used to help with the understanding of the question

"David Portas" <RE****************************@acm.org> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.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
--

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

87 posts views Thread by Frances Del Rio | last post: by
27 posts views Thread by The Bicycling Guitarist | last post: by
22 posts views Thread by stevenkobes | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.