Need Query for update and insert in one go.

Hi All,
Please look at the following tables. I have Two tables
Data and TmpData with the following structure.

Data ( All int columns, ID column is Primary and identity)

Id UserID PrgID RoldID
322 1 1 2
323 1 2 2
324 1 3 2
325 2 1 2
326 2 2 2
327 2 3 2
328 3 1 2
329 3 2 2
330 3 3 2

TmpData
Id UserID PrgID RoldID
82 1 1 3
83 1 2 3
84 1 3 3
85 2 1 3
86 2 2 3
87 2 3 3
91 20 1 2
92 20 2 2
93 20 3 2
94 21 1 2
95 21 2 2
96 21 3 2

Now I need to run a query so that
Part 1: It updates existing RoleId columns ( Based on Userid,PrgID) in 'Data' Table with corresponding values from 'Data' table.
Part2 : It inserts new rows in 'TmpData' to 'Data' table.

I am done with Part1 using the simple update statement.

Update Data
Set Data.programroleid=tmp.ProgramRoleID
from TmpData tmp
where Data.userid=tmp.UserId
and Data.programId=tmp.ProgramId

This works fine for existing userids,programids in 'Data' and 'TmpData' tables. But I am struggling with inserting new rows into Data from 'TmpData' ( That exists only in 'tmpData' table). Based on above table structures how do I insert new data into 'Data' table from 'TmpData'.INSERT INTO Data(ID, UserID, PrgID, RoldID)
SELECT * FROM TmpData
WHERE ID not in (SELECT ID FROM TmpData Inner Join Data on TmpData.UserID=Data.UserID and TmpData.ProgID=Data.PrgID and TmpData.RoldID=Data.RoldID)|||ID is auto-generated identity value, and thus cannot be inserted into the target table (unless you specifically disable this for the transaction...).
I wouldn't expect the identity values to match between Data and TmpData anyway, so the "NOT IN" method is probably not appropriate. It is also not efficient. NOT EXISTS is faster than NOT IN.

INSERT INTO Data
(ID,
UserID,
PrgID,
RoldID)
SELECT ID,
UserID,
PrgID,
RoldID
FROM TmpData
WHERE NOT EXISTS
(SELECT *
FROM Data
WHERE Data.UserID = TmpData.UserID
and Data.PrgID = TmpData.PrgID
and Data.RoldID = TmpData.RoldID)