Update a table based on values from another

[kemorr]

Hi everyone,

I'm really stuck with this one, in my head it seems like a simple task.

Language T-SQL Platfom: SQL Server 2005

Background:
I have read access to a view of a table via a linked server setup, a copy of some of the column headings is located in tbl_LOSPT and stored within a database I have full access to.

The problem is the table gets appended and not updateed to throughtout the day for example;
On admission our patient administration system (PAS) automatically fires a trigger to append the record to the table stay_history this generates a stay_id number unique to the patients stay Lets say 1000. Now lets say the patient gets there care plan entered in the the PAS system instead of updating the orignal stay_id row (1000) it adds a new record (row) but issues the same stay_id

I am unable to make or get made any changes to the database I have a view from.

Goal:
To move the records from the view of stay_history into tbl_LOSPT in the order the events happend but only if the update was of certain columns

For example;

stay_history on the linked server contains the following
stay_history_id,stay_id,admission_date,PDD,EDD,War d,Consultant,Specialty,Other1,
Other2,Other3,Event_Code,last_update
1,1000,01/01/09,04/01/09,,F1,XYZ,GMED,4,gh,12,NA,01/01/09 21:22:00
2,1000,01/01/09,04/01/09,,F1,XYZ,GMED,5,gh,12,CP,01/01/09 22:22:22
3,1000,01/01/09,04/01/09,04/01/09,F1,XYZ,GMED,5,gh,12,CP,01/01/09 22:22:22
4,1000,01/01/09,04/01/09,04/01/09,F1,XYZ,GMED,5,gh,01,CP,01/01/09 22:22:22
5,1000,01/01/09,04/01/09,04/01/09,F1,XYZ,GSUR,5,gh,01,CP,01/01/09 22:35:00

So I want to append all new records where last_update > last_run_date (stored in log table) and event_code = 'NA' -- This bit is fine no problem but...
now I want to update the records where view.stay_id = tbl_LOSPT.stay_id but only if columns PDD,EDD,Ward,Consulatnt,Specialty have changed I DO NOT want the record if any other colomns have changed so in the above example I want to append Row 1 first, then I want to update that row with row 3 then row 5

I am using a trigger on tbl_LOSPT to copy the current version of the row to tbl_audit_LOSPT before update. Notice that the event code is the same on any of the records I want to update but not always also last_update is sometimes the same and sometimes not.

I know I am way off the mark with this code but;

UPDATE BRAVO.WSHDischargeTracking.dbo.vw_extract_stay_his tory_all dt INNER JOIN
dbo_tbl_LOSPT ON dt.stay_id = dbo_tbl_LOSPT.stay_id
SET dbo_tbl_LOSPT.planned_discharge_date = dt.planned_discharge_date, dbo_tbl_LOSPT.discharge_date = dt.discharge_date;


Any help and example code would be very gratfully received!

Kind Regards

[WendellB]

Unfortunately, very few of us are knowledgeable about T-SQL, and even fewer are comfortable with triggers. In principal, you should be able to do what you describe, although I'm a bit fuzzy on some of the details. Where is the "last_run_date" field being stored? Presuming it is in a table and not a parameter being passed into a stored procedure, you will need to join to that table in order to get the date. And you will need a CASE statement to determine whether or not tbl_LOSPT should be updated. Also I'm confused about where tbl_audit_LOSPT fits into the process as your prototype code doesn't mention it. I do have some experience in doing this sort of thing to record address changes, so if I can get my arms around the problem, I may be able to help.

[kemorr]

[quote name='WendellB' post='766894' date='23-Mar-2009 22:45']Unfortunately, very few of us are knowledgeable about T-SQL, and even fewer are comfortable with triggers. In principal, you should be able to do what you describe, although I'm a bit fuzzy on some of the details. Where is the "last_run_date" field being stored? Presuming it is in a table and not a parameter being passed into a stored procedure, you will need to join to that table in order to get the date. And you will need a CASE statement to determine whether or not tbl_LOSPT should be updated. Also I'm confused about where tbl_audit_LOSPT fits into the process as your prototype code doesn't mention it. I do have some experience in doing this sort of thing to record address changes, so if I can get my arms around the problem, I may be able to help.[/quote]
Thank you for your reply,

Last_run_date is stored in a table (tbl_audit_LOSPT) I have no problems with the joining the table for that purpose, its the CASE statement I am going to struggle with, in access I would have opened a record set and looked at each row in turn to by writeing a function, however in T-SQL I don't know how to open a recordset.

i.e. (Access Air Code)

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT CA, CB, CC, CD FROM dbo.vw_extract_stay_history_all WHERE last_update > " & Date()-
Set rst = dbs.openrecordset(strSQL)

If Not rst!CB Like str_From_tbl_LOSPT Then

....

Any pointers would be useful, again many thanks

[WendellB]

You aren't the only one that struggles with CASE statements. They really are only useful in populating results for a SELECT statement and varying the data based on conditions. However in a trigger, you can refer to .DEL fields as the old values and compare them to the current value. However in your case, the trigger will always need to run on the Insert case, so first you will need to run a select to see if a record already exists, and then if it does, compare the values in the selected record. T-SQL is a real challenge when you are trying to do this sort of thing, as it isn't really a procedural language, and that is made doubly difficult with the lack of debugging tools. I would probably throw in the towel and run a periodic batch process using VB or C# or Java or something and have it decide whether to append or update. 2005 does expose a fair bit of capability to high-level programming languages.