By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table dbo.Files
(FileId int identity(1,1),
FileData varbinary(max) null,
ContentType varchar(100),
OperatorIdCreated int null,
OperatorIdUpdated int null,
Updated datetime default(getdate()))
declare @FileId int = 1
declare @FileData varbinary(max)
declare @ContentType Varchar(100) = 'asdf'
declare @OperatorId int = 2
BEGIN
--In Memory Table to
DECLARE @MergeOutput TABLE
(
Id INT
);
--Merge needs a table to Merge with so using a CTE
WITH CTE AS (
SELECT @FileId as FileId)
--Merge
MERGE INTO [dbo].[Files] as T
USING CTE AS S
ON T.FileId = S.FileId
WHEN NOT MATCHED THEN
INSERT (
FileData,
ContentType,
OperatorIdCreated,
OperatorIdUpdated
)
VALUES(
@FileData,
@ContentType,
@OperatorId,
@OperatorId
)
WHEN MATCHED THEN
UPDATE SET
FileData = @FileData,
| FileId | FileData | ContentType | OperatorIdCreated | OperatorIdUpdated | Updated |
|---|---|---|---|---|---|
| 1 | null | asdf | 2 | 2 | 2024-12-02 15:55:10.100 |
declare @FileId int = 1
declare @FileData varbinary(max)
declare @ContentType Varchar(100) = 'i changed this'
declare @OperatorId int = 55
BEGIN
--In Memory Table to
DECLARE @MergeOutput TABLE
(
Id INT
);
--Merge needs a table to Merge with so using a CTE
WITH CTE AS (
SELECT @FileId as FileId)
--Merge
MERGE INTO [dbo].[Files] as T
USING CTE AS S
ON T.FileId = S.FileId
WHEN NOT MATCHED THEN
INSERT (
FileData,
ContentType,
OperatorIdCreated,
OperatorIdUpdated
)
VALUES(
@FileData,
@ContentType,
@OperatorId,
@OperatorId
)
WHEN MATCHED THEN
UPDATE SET
FileData = @FileData,
| FileId | FileData | ContentType | OperatorIdCreated | OperatorIdUpdated | Updated |
|---|---|---|---|---|---|
| 1 | null | i changed this | 2 | 55 | 2024-12-02 15:55:10.130 |
select * from Files
| FileId | FileData | ContentType | OperatorIdCreated | OperatorIdUpdated | Updated |
|---|---|---|---|---|---|
| 1 | null | i changed this | 2 | 55 | 2024-12-02 15:55:10.130 |