Automatically Create Joints

Automatically Create Joints
Hi,
I have a "tblShop" table in my database that holds the shop name and branch
locations like this:
tblShop
=======
shopID (smallInt)
shopName (varchar)
states (varchar)
The branch location (ie. the "states" field) is a comma-separated string of
the states that each shop has branches in (eg. "CA, NY, OR"). Now I'd like t
o
create a state table:
tblState
========
stateID (smallInt)
stateName (varchar)
and have a third table to join these 2 tables together like this:
tblShopState
============
ID (smallInt)
shopID (smallInt)
stateID (smallInt)
I have about a hundred thousand entries in the "tblShop" table. Is there
anyway I can write a procedure to run through this "tblShop" table, find out
what state each shop has branches in, and automatically create an entry in
the "tblShopState" table?
Thanks.http://www.sommarskog.se/arrays-in-sql.html
(also replied to same post in SQLJunkies forums)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"WB" <WB@.discussions.microsoft.com> wrote in message
news:2CC11302-EDF2-4807-8480-397A5AF940CA@.microsoft.com...
> Automatically Create Joints
>
> Hi,
> I have a "tblShop" table in my database that holds the shop name and
> branch
> locations like this:
> tblShop
> =======
> shopID (smallInt)
> shopName (varchar)
> states (varchar)
> The branch location (ie. the "states" field) is a comma-separated string
> of
> the states that each shop has branches in (eg. "CA, NY, OR"). Now I'd like
> to
> create a state table:
> tblState
> ========
> stateID (smallInt)
> stateName (varchar)
> and have a third table to join these 2 tables together like this:
> tblShopState
> ============
> ID (smallInt)
> shopID (smallInt)
> stateID (smallInt)
> I have about a hundred thousand entries in the "tblShop" table. Is there
> anyway I can write a procedure to run through this "tblShop" table, find
> out
> what state each shop has branches in, and automatically create an entry in
> the "tblShopState" table?
> Thanks.
>