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

Question

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?

score 2 · Accepted Answer · answered May 15 '13 at 15:29

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.

score 1 · Answer 2 · answered May 15 '13 at 15:19

I must confess to being a bit confused as to your intent given the various concepts you've referred to.

If you want to delete from the original table after your trigger fires in the update/delete then you're doing it wrong.

If you just want to keep a running total in another table for performance reasons then check out "indexed views".

If you want to add something to one table then update another and remove from the original then you are either looking for a queue or simply a stored procedure to perform the update on the appropriate table. You do not need to do complex steps with triggers and stuff.

No idea where the IDENTITY stuff comes from. Pretty sure you don't need it here.

I think you're making it more complex than it needs be.

I could be wrong - feel free to elaborate.

I think you might be right! I am using an INSERT statement from a web service in SharePoint... Perhaps I should investigate a MERGE statement instead of an INSERT and dump the intermediate table? — Shrout1, May 15 '13 at 15:28

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

2 Answers2