How to save and reuse the ApexSQL Log grid data via SQL BULK

Applies to
ApexSQL Log

Summary
Since retrieving the SQL transactions in the ApexSQL Log grid might be time-consuming, sometimes it’s better to have the results saved for later analysis. There are several ways to save the results. In this article we are going to explain how to save them into the database table via the Export to SQL BULK option.

Description
Once the results are shown in the main ApexSQL Log gird, the Export option on the Home tab in the menu provides exporting as XML, CSV, HTML, SQL script, and SQL BULK files.

The Export to SQL BULK option saves the transactions in the SQL database tables, so it can be queried and analyzed later, without using ApexSQL Log.

It creates three files: export.sql, exportOperations.dat, and exportDetails.dat.

The file named exportOperations.dat contains the main gird data, and exportDetails.dat contains the details shown in the Operation details panel.

The file named export.sql contains the SQL script which creates the database tables in which the transactions are saved. It also inserts the data stored in other two files.

To insert the exported data into tables, execute the script from the export.sql file, using SQL Server Management Studio, or any other database management tool.

The script creates two tables: the APEX_LOG_OPERATION table, which contains the data from exportOperations.dat file, and the APEX_LOG_OPREATON_DETAILS table, which contains the data from exportDetails.dat file. In order to create these tables in the specific database, add the following at the beginning of the script:

USE [database_name]
GO

The contents of the APEX_LOG_OPERATION and APEX_LOG_OPREATON_DETAILS tables are shown in the following pictures, respectively:

In order to read transactions for a specific table, the following script can be used:

SELECT *
FROM dbo.AUDIT_LOG_DATA
WHERE AUDIT_LOG_TRANSACTION_ID IN (
		SELECT AUDIT_LOG_TRANSACTION_ID
		FROM dbo.AUDIT_LOG_TRANSACTIONS
		WHERE TABLE_NAME LIKE 'Address'
		);

Similar code can be used to read the transactions created by a specific user:

SELECT *
FROM dbo.AUDIT_LOG_DATA
WHERE AUDIT_LOG_TRANSACTION_ID IN (
		SELECT AUDIT_LOG_TRANSACTION_ID
		FROM dbo.AUDIT_LOG_TRANSACTIONS
		WHERE Modified_By = 'Fujitsu\Milos'
		);

This method provides a faster access to the transaction data than starting a new ApexSQL Log session and reading the data sources again.

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.