Automatically Create Joints - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Automatically Create Joints

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2005-10-13 : 11:50:19

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.

W.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-10-13 : 12:09:51

"Automatically Create Joints"...would these be of the roll-your-own medicinal variety???

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-10-13 : 12:10:26

can you give us sample input data and sample matching results.

Kristen
Test

22859 Posts

Posted - 2005-10-13 : 13:09:06

Hi ywb, Welcome to SQL Team!

Somthing like this perhaps?


INSERT INTO tblShopState
SELECT [ID] = ???,
       [shopID] = SH.shopID,
       [statID] = ST.stateID
FROM tblShop SH
     JOIN tblState
        ON ',' + states + ',' LIKE '%,' + stateName + ',%'

Kristen

ywb
Yak Posting Veteran

55 Posts

Posted - 2005-10-15 : 03:24:40

Yes, Kristen, that worked! Thank you!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 01:57:09

Kris, are you a mind reader?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 04:33:44

Maddy, have you never done "Make lookup table for a picklist from whatever rubbish data is in there" rather than "Make nice codes maintenance table, and then use that for picklist for users to make new records"?

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-17 : 04:41:24

Usually I dont

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources