Delete two physical tables and insert those to separate two temp tables at once using SQL

Question

I'm having a SQL query which deletes records from a physical tables (header, line) and insert those records to a 2 separate temp table using OUTPUT DELETED.*.

DELETE FROM [dbo].[PartnerFilteredLines] 
OUTPUT DELETED.*
INTO @PartnerFilteredLines 
WHERE SettingsFileId=@SettingsSplitId

DELETE FROM [dbo].[PartnerFilteredHeader] 
OUTPUT DELETED.*
INTO @PartnerFilteredHeader 
WHERE SettingsFileId=@SettingsSplitId

Both 2 queries are working independently. My requirement is to somehow merge those 2 queries and do the delete part at one time (one execution) because for a development requirement this query is scheduled for 1 second. Therefore, sometimes @PartnerFilteredLines records are there and @PartnerFilteredHeader are not because they were executed in 2 executions. Can somebody help me to achieve this?

Thank you

Answer 1

You can use transactions to hold lock for table PartnerFilteredHeader

CREATE PROCEDURE ...
AS
BEGIN
  BEGIN TRANSACTION

  -- lock table "PartnerFilteredHeader" till end of transaction
  SELECT ...
  FROM PartnerFilteredHeader
  WITH (TABLOCK, HOLDLOCK)

  DELETE FROM [dbo].[PartnerFilteredLines] 
  OUTPUT DELETED.*
  INTO @PartnerFilteredLines 
  WHERE SettingsFileId=@SettingsSplitId
    
  DELETE FROM [dbo].[PartnerFilteredHeader] 
  OUTPUT DELETED.*
  INTO @PartnerFilteredHeader 
  WHERE SettingsFileId=@SettingsSplitId


  -- release lock
  COMMIT TRANSACTION
END