We have a table as follows:
CREATE TABLE dbo.Test (
StartDate DATETIME2 NOT NULL,
EndDate DATETIME2 NULL
Field1 NVARCHAR(50) NOT NULL,
Field2 NVARCHAR(50) NOT NULL,
etc.
)
If we insert a row the EndDate is always NULL. If it comes to an update we don't really update the existing row but we set the EndDate of it and insert a new one. Assuming there is a stored procedure for insert and update.
DECLARE @newStartDate DATETIME2
SELECT @newStartDate = GETDATE()
UPDATE dbo.Test SET (EndDate = @newStartDate) WHERE StartDate = @someDate
INSERT INTO dbo.Test (StartDate, EndDate, Field1, Field2)
SELECT @newStartDate, NULL, Field1, Field2
FROM dbo.Test where StartDate = @someDate AND EndDate = @newStartDate
It can happen that we need to set other start/insert values than the old ones. That can be done as follows.
INSERT INTO dbo.Test (StartDate, EndDate, Field1, Field2)
SELECT @newStartDate, NULL, @parameter, Field2
FROM dbo.Test where StartDate = @someDate AND EndDate = @newStartDate
The main problem is now that the real table contains more than 100 columns and the above approach would work but is very error prone.
I'm looking for a dynamic approach where the insert statement dynamically takes the parameter value if it exists.
In C# I would do something like this. existing
would be a Dictionary
containing every column and overrides
are the not default values that needs to be changed.
var existing= ..."SELECT TOP 1 * from dbo.Test"
foreach (var changeParameter in overrides)
existing[changeParameter.Key] = changeParameter.Value;
In existing
are now all values stored as they should be for the insert. Therefore I can build a new insert statement.
Is this approach somehow possible on the database? I don't want to load all data rows just to write it back right away. I would rather do everything directly on the database.