sql-server-2008t-sqlstored-procedurestriggersscope-identity

TSQL Use record currently being inserted to select data in another table

Very new to TSQL...

I have the following table called "tblinit":

Account_Num    UserID        Task_Percent
-----------    ------------  ------------
1              john.smith    0.75

I would like to update the "Task Percent" value in "tblRaw" below.

Account_Num    UserID        Task_Percent
-----------    ------------  ------------
1              john.smith    0.5
2              mary.mickle   0.9
3              don.donalds   1

My plan is to use a TSQL stored procedure executed by a trigger on insert into "tblinit". The stored procedure will move the data into "tblRaw" (either a merge or a delete and insert) and then truncate "tblinit" when the procedure is done. tblInit is only used to stage incoming data.

I have read about SCOPE_IDENTITY and @@IDENTIY but don't fully grasp the concept. Is the scope defined by the trigger which executes the stored procedure? In attempting my own SELECT statements using SCOPE_IDENTITY and @@IDENTITY I always return with a "NULL" result. The referenced MSDN article seems to return primary keys that don't correlate to the data specified in the article's example. Clearly I am reading something incorrectly. I want to grab the record that was just inserted and use it in my query.

In essence, how do I update john.smith's new percentage value automatically on insert or, alternatively, how do I add a new record entirely?

Solution

  • how do I update john.smith's new percentage value automatically on insert

    This trigger could be used to do exactly that:

    create trigger tblinit_to_tblRaw
on tblinit
for insert
as
begin
    update r
    set r.Task_Percent = i.Task_Percent
    from inserted i
        join tblRaw r on i.UserID = r.UserID -- Join on Account_Num instead?
end

    This does not take into account new records (no existing match in tblRaw). For that you might want to run if exists(... or merge.