In SQL how to INSERT, UPDATE or DELETE record in one statement

Question

I am trying to find a way, how I can look for one row in a table with a search for the id, but then I use this to give me the INSERT statement (which creates this row again)

What I am trying to do, is get an audit where any update - checks the state of a row, saves it as its INSERT statement and saves this to an audit file.

Answer 1

Student Details:

USE AdventureWorks
GO

CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)
GO
INSERT INTO StudentDetails
VALUES(1,'SMITH')
INSERT INTO StudentDetails
VALUES(2,'ALLEN')
INSERT INTO StudentDetails
VALUES(3,'JONES')
INSERT INTO StudentDetails
VALUES(4,'MARTIN')
INSERT INTO StudentDetails
VALUES(5,'JAMES')
GO

StudentTotalMarks:

CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)
GO
INSERT INTO StudentTotalMarks
VALUES(1,230)
INSERT INTO StudentTotalMarks
VALUES(2,255)
INSERT INTO StudentTotalMarks
VALUES(3,200)
GO

In our example we will consider three main conditions while we merge this two tables.

Delete the records whose marks are more than 250.
Update marks and add 25 to each as internals if records exist.
Insert the records if record does not exists.
Now we will write MERGE process for tables created earlier. We will make sure that we will have our three conditions discussed above are satisfied.

MERGE StudentTotalMarks AS stm
USING (SELECT StudentID,StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID,StudentMarks)
VALUES(sd.StudentID,25);
GO

There are two very important points to remember while using MERGE statement.

Semicolon is mandatory after the merge statement.
When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

After the MERGE statement has been executed, we should compare previous resultset and new resultset to verify if our three conditions are carried out.

SELECT * FROM StudentTotalMarks ORDER BY StudentID 
GO

Answer 2

Generally we write separate statements to INSERT, UPDATE or DELETE data based on certain conditions. But now in SQL Server 2008 there is a new feature present called MERGE statement using which we can INSERT, UPDATE or DELETE record in one statement.

MERGE is a new feature which Microsoft has introduced with SQL Server 2008 that provides an efficient way to perform multiple DML operations.

In previous version of SQL server, we had to write separate statements to INSERT, UPDATE or DELETE data based on certain conditions, but now using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.

One of the most imortant advantage of MERGE statement is all the data is read and processed only once.The MERGE statement internally works as an individual insert, update and delete statement within a single Merge statement.You need to specify the SOURCE and the TARGET table or query which should be joined together.

EXAMPLE:

MERGE WITH INSERT AND UPDATE

CREATE PROCEDURE MERGE_INSERT_UPDATE
   @USERNAME VARCHAR(50),
   @ROOM VARCHAR(20)
AS 
BEGIN
    SET NOCOUNT ON;

    MERGE USERS AS target
    USING (SELECT @USERNAME, @ROOM) AS source (USERNAME, ROOM)
    ON (target.USERNAME = source.USERNAME)
    WHEN MATCHED THEN 
        UPDATE SET ROOM = source.ROOM
 WHEN NOT MATCHED THEN 
     INSERT (USERNAME, ROOM)
     VALUES (source.USERNAME, source.ROOM)
     OUTPUT deleted.*, $action, inserted.* ;
END;
GO

EXEC MERGE_INSERT_UPDATE @USERNAME = 'Charlie bB', @ROOM = 'New ROOM';

MERGE WITH DELETE AND UPDATE

CREATE PROCEDURE MERGE_WITH_UPDATE_DELETE

AS
MERGE Rooms AS target
USING (SELECT Users.ID,Users.RENT FROM Users
    JOIN Rooms 
    ON Users.ID = Rooms.ID
    ) AS source (ID,RENT)
ON (target.ID = source.ID)
WHEN MATCHED AND target.RENT <= 450
    THEN DELETE
WHEN MATCHED 
    THEN UPDATE SET target.RENT = 5000

OUTPUT $action, Inserted.ID, Inserted.RENT, Deleted.ID,
    Deleted.RENT;
GO

EXEC MERGE_WITH_UPDATE_DELETE ;