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
Hi there,
I have written VERY similar MS SQL scripts.
I can help you edit it to include the intermediate (audit) table. It will be coded with utmost clarity in mind.
Thank you,
Goran
This is something I have done before and feel very comfortable in doing. I have 8 years of experience in doing SQL Server writing queries and Stored Procedures.
Hi Sir!
I'm Eliot,, and I can help you creating the process on your stored procedure for handling audit tables.
Please let me know if Interested,
Best regards.
Eliot