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 |
rjcom
Starting Member
3 Posts |
Posted - 2013-01-12 : 04:57:24
|
Hi All,I have three tables Food, Foodgroup, and FoodgroupCondition which I wish to take TVP then create first loop to use and iterate and pass FoodMatchString and FoodNotMatchString as variables (not written) and runs through the Food table and check those conditions and produces the fourth table Food_Foodgroup. Is this the right approach, I need help?My current sql cursor doesn't have the FoodNotMatchString, but I haven't figured out it to get that to work.----DECLARE @Food_FoodgroupID uniqueidentifier, @FoodID int, @FoodgroupID intSET @Food_FoodgroupID = NewID()DECLARE @FoodMatchString nvarchar(15)// wish to set @FoodMatchString from table value parameterSET @FoodMatchString = N'%beef%'DECLARE db_cursor CURSOR FAST_FORWARDFORSELECT Food.FoodIDFROM FoodWHERE Food.FoodName LIKE @FoodMatchStringOR Food.Ingredients LIKE @FoodMatchStringOPEN db_cursorFETCH NEXT FROM db_cursorINTO @FoodIDWHILE @@FETCH_STATUS = 0BEGIN// wish to set @FoodgroupID from table value parameterSET @FoodgroupID = '5' INSERT INTO dbo.Food_FoodgroupSELECT NEWID(), @FoodID, @FoodgroupID//only insert if not foundWHERE NOT EXISTS (SELECT Food.FoodID, Food.FoodName, Food_Foodgroup.Food_FoodgroupIDFROM FoodINNER JOIN Food_FoodgroupON Food.FoodID = Food_Foodgroup.FoodIDWHERE Food_Foodgroup.FoodID = @FoodIDAND Food_Foodgroup.FoodgroupID = @FoodgroupIDAND (Food.FoodName LIKE @FoodMatchStringOR Food.Ingredients LIKE @FoodMatchString) )FETCH NEXT FROM db_cursorINTO @FoodIDENDCLOSE db_cursorDEALLOCATE db_cursor---FoodgroupCondition table:FoodgroupConditionID FoodMatchString FoodNotMatchString FoodgroupID1 'beef' , 'beef fat' 12 'milk' , 'milky' 33 'carrot' , 'null' 24 'vegetable' , 'vegetable juice' 25 'soup' , 'null'' 46 'pie' , 'null' 5Food table: FoodID FoodName Ingredients1 'beef curry', 'beef, milk'2 'beef stew', 'beef, carrots'3 'vegetable soup', 'vegetable'4 pie crust' , 'beef fat'Foodgroup table:FoodgroupID FoodGroupName1 meat2 vegetable3 dairy4 soup5 beef6 baked goodsFood_Foodgroup table:Food_FoodgroupID FoodID FoodgroupID'342DAAB6-F95F-47AA-BA79-2B1E7064B019', 1, 1'B021A8A4-37AB-4EDA-B7C3-333A194E470B', 1, 3'95D72F10-F6CC-4C18-BC2F-58410E3AB340', 2, 1'647E067F-DD5E-44E6-A407-8BBBFBC314E5', 2, 2'6A3C320F-BC52-4A80-9373-9C1125D82857', 3, 2'A46744B3-9983-48A4-892D-9EDEEC3267BD', 3, 4'cfd05dc0-3b5a-4ed2-9bd5-9863a94923f4', 1, 5'6d253e81-c6e2-4148-affe-05060cd450d9', 1, 5'1b833946-f354-46a7-aeee-5e9d0a7219f0', 4, 6I am doing this manually and wish to automate. Also, I don't like cursor or loops and see if it can written using a set based approach.Any insight and help is much appreciated. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-12 : 12:50:45
|
whats condition based on which you put ids? it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
rjcom
Starting Member
3 Posts |
Posted - 2013-01-12 : 15:34:23
|
Thank you Visakhm, and sorry if I miss your point. -- whats condition based on which you put ids? The conditions based would be @FoodMatchString AND NOT @FoodNotMatchString first for the FoodName and Ingredients column then inserts a record to FoodCategory if it doesn't exist.This would include the @FoodNotMatchString SELECT Food.FoodIDFROM FoodWHERE Food.FoodName LIKE @FoodMatchStringOR Food.Ingredients LIKE @FoodMatchStringAND (NOT LIKE @FoodNotMatchString)-- it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?Yes, it is doing that for now but I'm thinking to maybe checking FoodName, Ingredients, other column separately. I can't imagine of the loop structure though.I still wish for TVP to be read and an iteration using set based approach, not cursors or loops. Is it possible, am I thinking wrong? quote: Originally posted by visakh16 whats condition based on which you put ids? it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2013-01-12 : 17:30:43
|
If you really want help, please post the data in a readily consumable format using INSERT/SELECT statements to populate a table.--Jeff Moden RBAR is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row".First step towards the paradigm shift of writing Set Based code:"Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column." When writing schedules, keep the following in mind:"If you want it real bad, that's the way you'll likely get it." |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-13 : 23:02:01
|
quote: Originally posted by rjcom Thank you Visakhm, and sorry if I miss your point. -- whats condition based on which you put ids? The conditions based would be @FoodMatchString AND NOT @FoodNotMatchString first for the FoodName and Ingredients column then inserts a record to FoodCategory if it doesn't exist.This would include the @FoodNotMatchString SELECT Food.FoodIDFROM FoodWHERE Food.FoodName LIKE @FoodMatchStringOR Food.Ingredients LIKE @FoodMatchStringAND (NOT LIKE @FoodNotMatchString)-- it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?Yes, it is doing that for now but I'm thinking to maybe checking FoodName, Ingredients, other column separately. I can't imagine of the loop structure though.I still wish for TVP to be read and an iteration using set based approach, not cursors or loops. Is it possible, am I thinking wrong? quote: Originally posted by visakh16 whats condition based on which you put ids? it seems to be matched from FoodName and Ingredients column alternatively. Is that correct?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
do you mean you need to do comparison against group of columns to determine if row is to be considered?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
rjcom
Starting Member
3 Posts |
Posted - 2013-01-14 : 05:20:29
|
Yes, Visakhm you are correct. If a SQL script is ran correctly, then Food_Foodgroup will be created. I have posted the consumable data to better assist with this. Thank you.DECLARE @Food TABLE ([FoodID] int NOT NULL,[FoodName] varchar(30), [Ingredients] text) INSERT @FoodSELECT 1, N'beef curry', N'beef, milk' UNION ALLSELECT 2, N'beef stew', N'beef, carrots' UNION ALLSELECT 3, N'vegetable soup', N'vegetable' UNION ALLSELECT 4, N'pie crust', N'beef fat'SELECT * FROM @FoodDECLARE @Foodgroup TABLE ( [FoodgroupID] int NOT NULL, [FoodgroupName] varchar(30)) INSERT @FoodgroupSELECT 1, N'meat' UNION ALLSELECT 2, N'vegetable' UNION ALLSELECT 3, N'dairy' UNION ALLSELECT 4, N'soup' UNION ALLSELECT 5, N'beef' UNION ALLSELECT 6, N'baked goods'SELECT * FROM @FoodgroupDECLARE @FoodgroupCondition TABLE ( [FoodgroupConditionID] int, [FoodMatchString] varchar(20), [FoodNotMatchString] varchar(20), [FoodgroupID] int ) INSERT @FoodgroupConditionSELECT 1, N'beef', N'beef fat', 1 UNION ALLSELECT 2, N'milk', N'milky', 3 UNION ALLSELECT 3, N'carrot', N'', 2 UNION ALLSELECT 4, N'vegetable', N'vegetable juice', 2 UNION ALLSELECT 5, N'soup', N'', 4 UNION ALLSELECT 6, N'pie', N'', 5SELECT * FROM @FoodgroupConditionDECLARE @Food_Foodgroup TABLE ( [Food_FoodgroupID] uniqueidentifier, [FoodID] int , [FoodgroupID] int )INSERT @Food_FoodgroupSELECT '342DAAB6-F95F-47AA-BA79-2B1E7064B019', 1, 1 UNION ALLSELECT 'B021A8A4-37AB-4EDA-B7C3-333A194E470B', 1, 3 UNION ALLSELECT '95D72F10-F6CC-4C18-BC2F-58410E3AB340', 2, 1 UNION ALLSELECT '647E067F-DD5E-44E6-A407-8BBBFBC314E5', 2, 2 UNION ALLSELECT '6A3C320F-BC52-4A80-9373-9C1125D82857', 3, 2 UNION ALLSELECT 'A46744B3-9983-48A4-892D-9EDEEC3267BD', 3, 4 UNION ALLSELECT 'cfd05dc0-3b5a-4ed2-9bd5-9863a94923f4', 1, 5 UNION ALLSELECT '6d253e81-c6e2-4148-affe-05060cd450d9', 2, 5 UNION ALLSELECT '1b833946-f354-46a7-aeee-5e9d0a7219f0', 4, 5SELECT * FROM @Food_Foodgroup |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-14 : 05:48:47
|
even then there's an issue. The values are not 100 % matchingfor example you've a value of vegetable soup in @Food whereas you've only value of soup in @Foodgroup table. So you cant just do a simple comparison to make them enter as record pair 3,4 in @Food_Foodgroup------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|