STMT IN SQL 2005

It's a two part issue - first, update the records that exist, and then insert the records that don't exist. Assuming R.No is the unique field, then something similar to the following should work:

UPDATE Student_Target st
SET st.RNo = ss.RNo, st.FirstName = ss.FirstName, st.LastName = ss.LastName
FROM Student_Target st INNER JOIN StudentInfoSource ss ON st.RNo = ss.RNo

INSERT INTO Student_Target st
SELECT ss.RNo, ss.FirstName, ss.LastName
FROM studentInfoSource ss
WHERE ss.RNo NOT IN (SELECT st2.RNo FROM Student_Target st2)

(untried and untested)

There are other ways, of course...

You'll probably want to wrap those two statements in a TRANSACTION in order to ensure data consistency; and you also might want to think about tidying up the Source table.

This is true that there is no merge statement in SQL Server 2005, so you have to use 3 statements to do the work. It is best to do the update first because if insert is performed before update then the newly inserted records will also become a subject of the update, and while it does not do any harm, it is clearly a waste of resources. Since from the question definition it is not possoble to figure out which columns need to be updated and which are used for matching, I will assume that the column named R.No (I will call it R_No in the script below) is the key and the names columns are a subject of an update statement

begin tran;

-- update only those rows which are already present in the destination
update dest
set
    FirstName = src.FirstName,
    LastName = src.LastName
    from STUDENT_INFO src inner join STUDENT dest
        on src.R_No = dest.R_No;

-- delete from the destination table those rows which are
-- no longer present in the source table
delete dest
    from STUDENT dest left join STUDENT_INFO src
        on src.R_No = dest.R_No
    where src.R_No is null;

-- finally, insert new records (flip the join to do so)
insert into STUDENT (R_No, FirstName, LastName)
select
    src.R_No, src.FirstName, src.LastName
    from STUDENT_INFO src left join STUDENT dest
        on src.R_No = dest.R_No
    where dest.R_No is null;

commit tran;
go

Oleg