Database Mail Merge from Traditional Databases

TL;DR Generate Word/PDF documents from SQL Server, Oracle, and MySQL using SQL queries.

Database Mail Merge

In every organization, the database plays an important role in holding the entire organization’s data. Traditional databases such as IBM, Microsoft & Oracle control close to 80% of the DBMS market. Oracle, MySQL, and SQL server occupy the top positions in the latest DB engine rankings.

For organizations that generate loads of business documents, manual creation of documents from database records is a costly and time-consuming task. Automating the document creation from data sources improves employee productivity.

In traditional databases, data is stored in tables and they have a logical connection among them. Hence, Database solutions don’t have document generation capabilities. Hence organizations look for tools similar to Microsoft Word mail merge that automate the Word/PDF generation from DB data.

Database Mail Merge Demo

Word and PDF Docs from Database with EDocGen

Reporting tools such as Tableau, SQL Server Reporting Services, and SAP Crystal Reports are popular choices for the database mail merge. These are good for interactive reports but not for complex document generation. First, you've to design the document template afresh junking the existing templates. Secondly, the document designers in these tools only support limited dynamic fields. They are not business user friendly and working on them is frustrating.

The document templates undergo frequent changes. Hence it’s a good idea for business users to have a DIY database mail merge system. That reduces their dependency on the IT department and gives the flexibility to change templates and generate documents from DB data as-and-when required.

EDocGen is the best solution for business users to generate documents in bulk and on-demand from traditional databases. They can create/edit templates in Word and PDF editors. Utilizing existing Word and PDF templates, they can create business documents from databases.

1. Bulk document creation - Produce high volumes of documents from database data.
2. On-Demand document creation - Real-time demand document creation as part of work-flow. Ex: insertion/update/deletion of a record in database triggers document generation.

In this article, we limit ourselves to Oracle, MySQL, and SQL server databases as they are the top three database engines. You can extrapolate this to other databases. The system supports both on-premise (local) and online cloud databases.

Database mail merge- Bulk/Batch Documents

Using EDocGen database mail merge, generate documents from databases in two steps.

1. Connect to your database from the system.
2. Generate documents from query data.
3. Optionally, you can choose to send generated documents to individual recipients.

Oracle Database mail merge

Step1: Connect to Oracle Database

You can connect to both local and publicly hosted Oracle databases. In the connection screen, select the Oracle database from the drop-down. Then enter the connection URL in "username@hostname[:port]/DatabaseName" format and the password. For a local database, your connection URL would look like root@localhost/oracledb.

For select query, you can use native SQL queries. Writing SQL is a one time job and SQL can be changed easily whenever the template changes. Many business users are adept at SQL.

The next step is to populate this SELECT statement data into the document template.

Step2: Generate Documents from query data

The system supports the filling of dynamic text, tables, blobs, and images from data into document templates. Briefly, you can

1. Populate data as-is without any data transformation. You can map the dynamic fields select statement columns. Thus, database changes would never affect the document creation. If the names of the template's dynamic fields match with SELECT columns, the system auto-maps for you. For repeat data population, this saves a lot of time.
2. Download generated documents or distribute them through email. You can send individual generated documents through your business email provided your query has an email column.

If your organization's security policies prohibit database access to business users, you may generate documents from XML/JSON/Excel files. Please visit XML to Word and JSON to Word blog for a detailed description on how to go about population into Word templates. Apart from Docx documents, you can also generate PDF documents. Please visit XML to PDF for details.

MySQL Mail Merge

Similar to the Oracle database, you can connect to both on-premise and cloud MySQL databases.

For select query, you can use native MySQL queries to fetch data. Follow the same steps above to generate documents from MySQL.

SQL Server Mail merge

As in above two database engines, you can use native SQL Server queries to fetch data. You can generate documents from both on-premise and cloud SQL Server instances.

The system is built for high performance to produce a large number of Word/PDF documents in a few seconds. It supports multi-lingual, very complex document generation.

On-Demand Database mail merge

Users can request the generation of documents and instantly view them on their browsers or use them as part of the workflow. An example workflow is - your web visitor fills a form on your corporate website and the system auto-sends a proposal PDF document to the mail id. With databases, every time a new record is inserted/updated a document conforming to your branding guidelines get created and sent.

EDoGen is API first product with a well-designed REST API. You can connect to your databases to produce documents on-demand.

Oracle REST Data Services (ORDS) enables users to turn their Oracle databases into RESTful API Services. There are a lot of tools and techniques for creating REST API for SQL Server and MySQL.

You can extend the integration to create complex document workflows. You may send the generated documents to cloud storage or E-Sign etc.

EDocGen automates document generation from almost all leading database engines. Give it a try by registering.