INSERT and DELETE in the same transaction?
<p>I have a Temp_Table with some data. Based on the data in the Temp_Table, I will delete the related rows from other tables, and after that, insert all data from the Temp-table to table1. Like the example below. </p>

<p><strong>In what way could I place a lock on Server2.Table1 (exclusive I supose) and run both the Delete and Insert statements before letting go of that lock?</strong> I don't want anyone to read or write to Table1 while I am doing my Delete / Inserts. Note that the Delete must be run <strong>before</strong> the Insert statement.</p>

<p>Is there a way is SSIS to do this? Or can I use Begin transaction in the beginning and commit transaction after the statements? I fear that both the Delete and Insert will run simultaneously while in the same transaction..</p>

<pre><code>DELETE Table1 
FROM Table1 t1
    INNER JOIN (    
       SELECT Column2 FROM Temp_Table
       GROUP BY Column2
    ) t2 ON t1.Column2 = t2.Column2

INSERT INTO Table1
SELECT (Column1, Column2...)
FROM Temp_Table
</code></pre>