asked
STMT IN SQL 2005
I want to implement merge statement in sql server 2005,since merge stmtn cannot be used in sql 2005. so i have to do a insert update and delete. I have two tables for eg STUDENT (TARGET)
R.No FIRST NAME LAST NAME STUDENT_INFO (SOURCE)
R.No FIRST NAME LAST NAME
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
