How can an INSERT from SELECT deadlock with itself (same SPID)
I have an Agent Job that runs a single stored procedure from a signle scheudle, one time per day. That procedure TRUNCATEs a table and then repopulates in using an INSERT FROM SELECT. The SELECT has a UNION and both parts use some of the same tables.
How can this procedure deadlock with itself?
Looks like intra-query parallelism. My first suggestion would be to try changing the INSERT/SELECT to use OPTION (MAXDOP 1).