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);