kanden
asked on
Insert Multiple Rows from XML or CSV (data not file)
Hello,
Here is my table structure
[tbl_items]
item_id int IDENTITY
item_title varchar (255)
[tbl_media_tags]
media_tag_id int IDENTITY
item_id int
tag_id
[tbl_tags]
tag_id int IDENTITY
tag varchar(255)
I need to create a stored procedure that accepts a two parameter @tags that can contain either a comma separated string or xml data of [tag_title]s. Also a @item_id parameter that contains the item_id of the item that I'm adding tags to. I need to:
1. Insert a new row into the [tbl_tags] table for each item in the @tags parameter that don't already exist in the [tbl_tags] table, then I need to find the ids of the tags that already exist and finally add a row for each [tag] into the [tbl_media_tags] table with the @item_id for the [item_id] and the [tag_id] for each new and existing tag
Inserts row
Here is my table structure
[tbl_items]
item_id int IDENTITY
item_title varchar (255)
[tbl_media_tags]
media_tag_id int IDENTITY
item_id int
tag_id
[tbl_tags]
tag_id int IDENTITY
tag varchar(255)
I need to create a stored procedure that accepts a two parameter @tags that can contain either a comma separated string or xml data of [tag_title]s. Also a @item_id parameter that contains the item_id of the item that I'm adding tags to. I need to:
1. Insert a new row into the [tbl_tags] table for each item in the @tags parameter that don't already exist in the [tbl_tags] table, then I need to find the ids of the tags that already exist and finally add a row for each [tag] into the [tbl_media_tags] table with the @item_id for the [item_id] and the [tag_id] for each new and existing tag
Inserts row
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, this was perfect. Is this the way you would do this same task? Just to know if I was on the right track or not.
Looks like a good method to me as keeps data nice an normalized with lookup (associative table) with only int keys in it. Can always have a view that joins all three for easy reporting/querying later but good design. Could also forego the extra PK column in media table and make the combination of item_id and tag_id as joint PK as you only need one instance of those two fields in tandem but either can appear multiple times independently without conflict.
HTH
Happy coding,
kev
HTH
Happy coding,
kev
+parses tag_title element from @tags
+inserts values that are not in TBL_TAGS
+insert into media table static item_id passed in variable and tag_ids matching the values from @tags
Assumes that each tag title can only be in the tags table once, given you are not inserting existing titles. If this is wrong, let me know.