I have a stored procedure in my database. It is an Insert or Update Procedure from Temporary Table to Base Table:
Temp_Item_Table and B_Item_Table
My Stored Procedure is Procedure_Upsert_Item in which it will check first if a row in TEMP_ITEM_TABLE already exist in B_ITEM_TABLE. If so, then it will update the row else it will insert the row then so on.
My question is I need to execute the procedure using batch file in which I did like this:
It will only display the rows in my TEMP_item_Table. Result : Procedure
"Procedure_Upsert_Item" expects the parameter which is not supplied.
How will I do it like this or any other way
that instead of putting specific parameter for the procedure, I just need to select all the rows in my temp table then pass through the procedure then end up to my base table?
For any assistance on this. Any help is very much appreciated.
Add single quotation and remove bracket in the stored procedure parameter. i.e
"Execute dbo.Procedure_Upsert_Item 'Select * from Temp_Item_Table'"
I am not sure why you are passing SELECT string in the parameter. We can achieve what you need using MERGE command.
MERGE Production.UnitMeasure AS target
USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
UPDATE SET Name = source.Name
WHEN NOT MATCHED THEN
INSERT (UnitMeasureCode, Name)
VALUES (source.UnitMeasureCode, source.Name)
OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;