complex insert problem

I am not really sure how to phrase this problem so I have a picture to help

Okay the tables above allow me to create types of data for pages that collect small amounts of info without me having to create hundreds of small tables

so one type of data I collect is Employee First Aid training - that type of data is made up of three attributes EmployeeID (ID 3 in the Attributes table), FirstAidTrainingDate (ID 4 in the Attributes table), LocationID (id 6 in the attributes table)

This data is to be saved in the Data Table as rows

so the data table looks like this assuming my DataThing is number 10 in the Objects table and its Type 1 in the Types table the data table will record

10,3,83

10,4,10/25/2007

10,6,35

My question is this without having to do three insert statements is there a way to pass in a pipedelimited string and have some dynamic sql do all the inserts for me?

If this makes no sense please tell me what a biter I am :)

From where do you get the data to insert?

|||

Yes, it is possible to write a stored procedure that would accept a delimited string and use dynamic sql to insert / update / delete the data.

If course, that stored procedure will have to issue three insert statements or three delete statements. If you sent old/new values and checked for differences, you could cut down on the number of update statements that will be issued, otherwise you will be issuing 3 dynamically created update statements. :)

Now, there is a totally different approach you could take.

Are you familiar with instead of triggers on views?

The information that you have in your tables is sufficient
(with the possible addition of a column or three - I didn't spend the time to check)

to generated the sql to create one view and instead of trigger per "row" of data. I refer to this type of object as "compile-time dynamically generated", versus "run-time dynamically generated". If the definitions of your FARTS does not change all that often, you get the advantage of generic code without the full run-time penalty for having it.

This would allow you to dynamically issue insert/update/delete statements to the view, And it would be pretty nifty for reporting also.

The key to making the query in the view work is to create a function that returns the correct FartData Data value for a given row, by passing in the row id and the attribute id.

Your select query would look like this:

select
id
,dbo.GetFartData(id, 3) as EmployeeID
,dbo.GetFartData(id, 4) as FirstAidTrainingDate
,dbo.GetFartData(id, 6) as LocationId
from ...

Let me know if you need more info on these ideas.