SQL Server 2012: we have a stored procedure that truncates an etl table, populates it with a dataset, does some updates/lookups/inserts/etc and then depending on the parameters passed to it, updates the original data.
That stored procedure is currently called in 5+ SSIS packages and in a handful of other stored procedures. Due to the truncation, we've been very careful to stagger the run times of all those processes to not have anything overwrite each other, but do we need to be?
If I call that stored procedure 1 time and it truncates and then populates the table with data and starts doing its thing, and then that proc gets called again from another source while the other is still running, is there a concern that the truncate and updates will get crosswired and some data will get lost or updated incorrectly? Or will SQL handle the transactions independently and it should be ok? I know the ACID definition and it feels like we should be ok to run things whenever, but I'm just curious if there's anything else we should to to ensure that multiple calls to the same proc get handled properly.