Create New Tables from Single Table Based on Filter using Bold ETL
In this article, we have imported data tables from the SQL server, implemented data transformation processes through the utilization of SQL scripts to generate new tables by applying filters and joining existing tables, and subsequently transferred the modified data to the target database utilizing the Bold ETL tool.
Follow the step-by-step process below:
Step 1: Click on the Bold ETL icon to open the Bold ETL site in a new tab.
Step 2: Click Add Project in the left-side panel. Enter the project name and click the tick icon.
Step 3: Click on Project name. It will open yaml editor to configure the source and destination connector configuration.
Step 4: Click MSSQL in the left-side panel and add the template in the right-side panel to add the sample configuration in the yaml editor. Replace your DB credentials and tables as shown below.
Step 5: Click Save and choose the destination, then click the Yes button.
Step 6: Go to Schedules and select Run Now option in the context menu of the data source grid.
Step 7: Logs will be available in the Logs tab. Click the project name in the left side panel and switch to the Logs tab.
Step 8: Go to the Transformation tab and click Add Table. Enter the table name to be moved into the destination database.
Step 9: Tables will be moved into destination schema named Project Name. E.g., if the project name is test, it will be moved to the test schema.
Refer to Transformation-Preview
Step 10: Enter the SQL scripts to apply transformation on existing tables and create the SQL scripts into a new table in the destination database.
| Tablename | SQL Script |
| FreshDetails | select * from copytables.monitortable where descrp = 'Fresh' |
| ExitDetails | select * from copytables.monitortable where descrp = 'Exit' |
| CombinedDetails | select t1.id,t1.date,t1.count as freshcount,t1.descrp as freshdescrp, t2.count as exitcount,t2.descrp as exitdescrp from (select * from copytables.monitortable where descrp = 'Fresh') t1 full outer join (select * from copytables.monitortable where descrp = 'Exit') t2 on t1.date=t2.date |
Step 11: Click Save. Then go to “Source” or “Load” tab and save the project again for Run Now on the Schedule page
Step 12: Then, go to the Schedules page and select Schedule option in the context menu of the data source grid. Configure the schedule and click Run.
Step 13: As per the schedule, move the transform tables and source tables into the destination database, and create a data source with a random table in Bold Reports.
Step14: Go to the Dataset page and create a dataset with the existing data source created in the previous step.
- Drag and drop the table that will be used for the report.
Refer: Working with ETL
