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.
| Author |
Topic |
|
DeluxeEdition
Starting Member
1 Post |
 Posted - 2005-04-21 : 18:34:18
|
| I'm not sure how to ask this question or if what I'm asking is even possible. Please be patient with me and ask me any questions if I haven't explained myself well.Say I have a list of 6000 restaurants that I want to put in 50 different categories based on the name of each resturant. Each restaurant can go into up to 4 different categories. I want the database (SQL 2000) to be able to do this with a one time run on the database side I'm assuming there is an SQL statement that would work but have no idea how.Ex. Lou's Chinese Restaurant - the database sees Chinese in the namefield and adds 19 (representing Chinese) into the category1 fieldEx2. Mel's French and Japanese Delicatessen - the database sees French in the namefield and adds 22 (representing French) into the category1 field and sees Japanese so adds 8 (representing Japanese) into the category2 field.Ex3. Fred's Cajun Caribbean Carryout Cafe - the database sees Cajun in the namefield and adds 14 (representing Cajun) into the category1 field and sees Caribbean so adds 6 (representing Caribbean) into the category2 field. It then sees Carryout and adds 24 to category3 field along with Cafe which it adds 10 to category4 field.I know this isn't the best example but hopefully it gets my point across. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-21 : 18:49:03
|
| I'm thoroughly confused. Are you wanting to write INSERT statements to pre-populate the database? If so, please check out INSERT INTO in SQL Server Books Online.Tara |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-04-21 : 18:57:11
|
| Whoa! I see red flags waving here. From the description you gave, it sounds like you have four different category fields all in the same table as the restaurant itself? This really should be a separate (junction) table which will map Restaurant to Category.Then, you need to break apart the restaurant name using Rob Volk's CSV Parsing trick except you are breaking on spaces not commas. Then when you have that result broken out, you have to join the Category table on CategoryName in order to get the Category ID.It can all be done, but is a bit of work. If you'll give us your DDL (CREATE TABLE statements) and some DML (INSERT statements to populate with sample data) for the Restaurant and Category tables, we can give you a more specific solution.---------------------------EmeraldCityDomains.com |
|
|
|
|
|
|
Database Automatically adding Data to Fields