I have an SQL script command as follows:
IF NOT EXISTS (SELECT 1 FROM MyTable WHERE TimeStamp = '$CurrentDate$' AND Plant = $CurrentPlant$)
BEGIN
INSERT INTO MyTable
(TimeStamp_UTC, Plant, LoadKWh, LoadKVarh, GenerationKWh, GenerationKVarh)
VALUES
(
'$CurrentDate$',
$CurrentPlant$,
$Filedata Column(3)$,
$Filedata Column(4)$,
$Filedata Column(5)$,
$Filedata Column(6)$
)
SELECT 1
END
ELSE
BEGIN
SELECT 0
END
It checks to see if a record exists, and if it doesn't, it will insert it then return a dataset containing a single row with a single field with the value of 1.
If it does already exist, it will not insert anything and it will return a dataset with a single row with a single field with the value of 0.
The insert works properly and records are being inserted into my database. However, the dataset of "SELECT 1" after the insert is not being returned. If I create a loop to access the dataset return from that script, it will cause my task to exit this loop as well as all loops above this command (in my case I have 3 levels of loops and it exits all three... continuing the task on the next iteration of the upper most loop).
However, if the insert is not required and the "SELECT 0" dataset is returned, then there are no problems and I can access the value of 0 from $Dataset Column(1)$.
Is there a problem with doing an insert followed by a select in my script? I have tested the code in SQL Server Management studio and it runs fine and returns 1 if a record was inserted and 0 if a record was not inserted.