Write a stored procedure SQL 2012

I have a stored procedure that BULK INSERTs some data from a csv file to a table. There are many different tables so the sproc picks up the tablename and csv file format and filename as input parameters to upload the data. Pertinent part of the code SET @sql = 'BULK INSERT '+@DestTableName+' FROM ''' + @LoadDataFileName + ''' WITH (FORMATFILE='''+@LoadFormatFileName+''')'; EXEC(@sql); I now want to add an intermediate audit table to this, so I have an audit of which rows were inserted and which rows 'failed' the insert. The audit tables will have the same structure as the original tables plus a few extra fields like, UploadID, Auditdtae datetime default getdate(), Loginname default suser_sname, UploadSuccess Yes/No. The sproc should check first if the audit table exist or not as the Audit table name will be +@DestTableName + '_Audit'. If it does not exist the sproc will create it. Process 1. BULKINSERT all the data to the Audit table (generating the next UploadID value (INTEGER)). 2. Cursor your way through all the rows of this UploadID and TRY/CATCH INSERT into the actual table. 3. If successfully inserted, then Mark the audit table row as UploadSuccess as Yes 4. If row fails to insert, then Mark the audit table row as UploadSuccess as No I hope this is clear. Thanks Aasim