Automatically Create Joints

Question

Automatically Create Joints

ywb

SSC Enthusiast

Points: 162
More actions
October 13, 2005 at 10:23 am

#91427

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

I know that there are more elegant ways of doing this but how about creating a loop for the tblState table that will look for stateName in States and then INSERT a record into tblShopState?

SELECT ShopID FROM tblShop WHERE States LIKE '%' + @LoopStateName + '%'

???

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

I agree with you AJ. It is not elegant, (I like bulldozer anyway ), but it is a one time shot and that is probably going to be the easiest, quickest, and best way to test for validity....

I wasn't born stupid - I had to study.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Well, you should check carefully how the states are stored in the concatenated column. Are the state abbreviations always 2 letters? If you have branches in Canada and have it stored as "CAN", then using the abovementioned code these would appear as being in California ("CA") as well... in such case you'd have to write something more complicated. So if your data are in a good shape, then the above is a good solution for one-time transfer (provided you test it first and check results thoroughly... it is never 100% reliable to do such things programmatically).

One more thing, this method won't report any errors, i.e. entries that are not recognizable (typos, states that are not included in tblState, etc.). It will just convert those that are OK and skip the rest without warning.

HTH, Vladan

David Burrows SSC Guru Points: 65143 More actions · Answer 4

This query

SELECT x.shopID,ISNULL(st.stateID,-1) AS [stateID],x.stateName

FROM (SELECT sh.shopID,

SUBSTRING(',' + sh.states + ',', n.number + 1,

CHARINDEX(',', ',' + sh.states + ',', n.number + 1) - n.number - 1) as [stateName]

FROM master.dbo.spt_values n

INNER JOIN tblShop sh

ON SUBSTRING(',' + sh.states + ',', n.number, 1) = ','

AND n.number < LEN(',' + sh.states + ',')

WHERE n.type = 'p' AND n.number > 0) x

LEFT OUTER JOIN tblState st

ON st.stateName = x.stateName

will produce a list of shopID,stateID and statename for all combinations in tblShop. stateID will contain -1 where state not found in tblState table. You can then use this to build your reference table tblShopState.

This will only work if tblShop.states is a max of 255 chars, if not then you can create your own number table.

Far away is close at hand in the images of elsewhere.
Anon.

RyanRandall SSChampion Points: 13623 More actions · Answer 5

I'm probably missing something, but the code below (in blue, between the stars) does the job on my simple dummy data. Run the whole lot below for a demo.

--create tables

create table #tblShop (shopID smallInt, shopName varchar(10), states varchar(200))

create table #tblState (stateID smallInt, stateName varchar(3))

create table #tblShopState (ID smallInt identity, shopID smallInt, stateID smallInt)

--insert dummy data

insert into #tblShop values (1, 'Shop 1', 'CA, NY, OR')

insert into #tblShop values (2, 'Shop 2', 'CA, NY, MI')

insert into #tblShop values (3, 'Shop 3', 'NY, MI')

insert into #tblShop values (4, 'Shop 4', 'OR')

insert into #tblShop values (5, 'Shop 5', 'CA, CAN')

insert into #tblState values (1, 'CA')

insert into #tblState values (2, 'NY')

insert into #tblState values (3, 'OR')

insert into #tblState values (4, 'MI')

insert into #tblState values (5, 'CAN')

--populate tblShopState

--***********************************************************************

insert into #tblShopState

select shopId, stateId

from

#tblShop a

inner join #tblState b on ',' + replace(a.states, ' ', '') + ',' like '%,' + b.stateName + ',%'

order by shopId, stateId

--***********************************************************************

--show results

select * from #tblShopState

--tidy up

drop table #tblShop

drop table #tblState

drop table #tblShopState

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

ywb SSC Enthusiast Points: 162 More actions · Answer 6

Yeah, Grasshopper! That's it!

A simple and elegant solution.

Thank you very much.