SQL Server Upsert

In SQL Server it often happens that you want to either insert or update a table row. You could first check the row count via select statement. Or you could check the @error variable after update. Nothing wrong, but wouldn’t it be convenient (and more performant) to do all of this in one statement. You can do this using the MERGE statement. In this case I update/insert into table Node with primary key node_id.

	MERGE INTO dbo.Node AS target
	USING
	(SELECT @node_id) AS source (node_id)
	ON target.node_id = source.node_id  
	WHEN MATCHED THEN
		UPDATE        
		SET	node_id = @node_id
			, nodenr = @nodenr
			, street = @street
			, housenr = @housenr
			, active = @active
			, project_id = @project_id
			, cloudnr = @cloudnr
	WHEN NOT MATCHED THEN
		INSERT (node_id, nodenr, street, housenr, active, 
                        project_id, cloudnr)
		VALUES (@node_id, @nodenr, @street, @housenr, @active, 
                        @project_id, @cloudnr);

If you have a primary key consisting of multiple fields, you have to change the select statement as follows:

MERGE INTO dbo.NodeStatus AS target
	USING
	(SELECT @node_id, @time, @status_type, @node_name) AS source 
        (node_id, [time], status_type, node_name)
	ON target.node_id = source.node_id and target.[time] = source.[time] 
        and target.status_type = source.status_type and target.node_name = 
        source.node_name  
	WHEN MATCHED THEN
		UPDATE        
		SET
			node_id = @node_id
			, [time] = @time
			, status_type = @status_type
			, node_name = @node_name
			, node_value = @node_value
	WHEN NOT MATCHED THEN
		INSERT (node_id, [time], status_type, node_name, node_value)
		VALUES (@node_id, @time, @status_type, @node_name, 
                        @node_value);