What approach should I take to split a string and update muliple SQL tables?

Question

I have an Article object with the following properties:

• Article Name (varchar)
• Article Body (varchar)
• Article Tags (varchar)

The Article Tags is a space delimited string of text values that a user has tagged the aritlce with. The 3 properties are passed to a stored procedure.

I now want to do the following:

1) Insert the Article Name and Article Body in the dbo.Articles table and return the article id

2) Insert each value of the Article Tags string in the dbo.Tags table as a separate row but first check for duplicates. If duplicate then return the tagid of existing record, if new record then return the new tagid

3) insert the article id and each tagid as a separate row in the dbo.TagMap table

I need to know the best approach to how I would accomplish this, also being conscious that it does not cost too much in performance.

Thanks in advance for any help you can provide.

Answer 1

I would do most of the work in your application rather than within sql. You could use one of the many sql split routines found online in conjunction with CROSS APPLY to add multiple rows to your tables, but I wouldn't do it. Keep it simple and have your application manage the tag inserts.

Let's say you have an article with 5 tags. That's one database call to create the article and return the article id back to your application (step #1 in your question.) And then, for each tag, you provide the article id and tag value in a separate database call which handles step #2 and step #3. You're looking at 6 really quick, not-at-all complicated operations for an article which most likely has a greater than average number of tags. That's really not all that bad.

But that's just my two cents. I hope it helps.

Answer 2

Personally, I would pass the tags as XML instead of a raw string. But, I much prefer the xquery capability of SQL Server than raw TSQL string parsing. The type of approach I'm talking about is briefly described in Passing lists to SQL Server 2005 with XML Parameters. The rest is standard "insert if not exists" logic. There is a bit more on that in SQL Server: Best way to Update row if exists, Insert if not. They are dealing with updates, but logic is similar.