SQL Job stuck executing an SQL Merge?

Question

I created a 2 step job at SQL Server 2008 which creates a table and then executes a MERGE command.

Here are the steps:

1. Create Table:

   CREATE TABLE CSVTable (AccountID INTEGER, UserType NVARCHAR (50), 
                          Lname NVARCHAR (50), Fname NVARCHAR (50), Mname NVARCHAR (50), 
                          Address NVARCHAR (100), Gender NVARCHAR (10), Birthdate DATE,
                          ContactNo NVARCHAR (50), Email NVARCHAR (50), Password NVARCHAR(30), EPurseBalance FLOAT, AccountStatus NVARCHAR (50))
   GO
   

2. SQL Merge:

   BEGIN TRAN;
   
   MERGE tblAccount AS T
   USING CSVTable AS S ON (T.AccountID = S.AccountID) 
   
   WHEN NOT MATCHED BY TARGET
      THEN INSERT(AccountID, UserType, Lname, Fname, Mname, Address, Gender, 
                  Birthdate, ContactNo, Email, Password, Balance, AccountStatus) 
           VALUES(S.AccountID, S.UserType, S.Lname, S.Fname, S.Mname, S.Address, S.Gender, 
                  S.Birthdate, S.ContactNo, S.Email, S.Password, S.Balance, S.AccountStatus)
   
   WHEN MATCHED 
      THEN UPDATE SET T.AccountStatus = S.AccountStatus;
   GO
   

The problem with this is that the SQL job takes forever to execute. It some sort entered into a loop which it can't escape. However, the Merge command works flawlessly if executed as a normal query. I don't know what causes this SQL Job problem.

Can someone help me? The job is still executing as I finish this post.

Any help will be greatly appreciated!!

Answer 1

I can't help but notice that you've got a begin tran with no commit. Either add a commitor, since there's only one statement in the batch, forego the explicit transaction altogether.