MSSQL - INSERT INTO VALUES only if all Values are not null
So i have this query
"INSERT INTO tblAssetRelations (ParentAssetID, ChildAssetID, Type)
VALUES (
(SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName = $field2),
(SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName = $field1),
12
);"
how can i cancel/abort the INSERT statement if one of the SELECT statements in VALUES returns no value(null)
ps: $field1 and $field2 are variables for a powershell script
=====================================================================
will something like this work? im not sure about the syntax.
declare @Variable1, @Variable2;
set @Variable1 = SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName= $field2
set @Variable2 = SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName= $field1
if @Variable1 IS NOT Null AND @Variable2 IS NOT Null
INSERT INTO tblAssetRelations (ParentAssetID, ChildAssetID, Type)
VALUES (@Variable1, @Variable2, 12);
Solution 1:
You can try using INSERT INTO ... SELECT
statement:
INSERT INTO tblAssetRelations (ParentAssetID, ChildAssetID, Type)
SELECT a, b, c FROM (
SELECT
(SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName = $field2) AS a,
(SELECT tblAssets.AssetID FROM tblAssets WHERE tblAssets.AssetName = $field1) AS b,
12 AS c
) WHERE a IS NOT NULL AND b IS NOT NULL
Then you would abort the INSERT
with WHERE a IS NOT NULL AND b IS NOT NULL