Creating Database Scripts from the Schema Map

A feature available in Data Sync, that you may not already know about, is the ability to generate script to create new tables in your Database based upon the columns you add to the schema map window.

You can edit the configuration before generating the script; such as changing the column names, data types and data lengths.

To generate the script you simply click a button.

We then go a step further and enable you to connect to your database and run the script from Data Sync. Before running the script if you want to add to it you can.

This works in a similar way to the SQL Query Tool.

Requirements

Before getting started you need to ensure you have the following:

• Windows 10 or Windows Server
• Downloaded & Installed Data Synchronisation Studio
• A dataset to base the script off
• Access to a database (either SQL Server, MySQL or PostgreSQL)

If you are connecting to PostgreSQL or MySQL please make sure you have the relevant ADO.net drivers installed.

• MySQL: https://dev.mysql.com/downloads/connector/net/
• PostgreSQL: https://github.com/npgsql/npgsql/releases/tag/v4.0.12

If you do not have Data Synchronisation Studio you can get a free evaluation edition.

Connect to your Source

You need to start by connecting to a datasource that you can base your schema off. This might be the dataset you want to ultimately import into your new table once it has been created.

In this example we have a simple CSV file with a number of columns describing contacts. You can use any of the providers built into Data Sync or you can write your own to connect to your business systems.

To connect to a CSV file you can either drag and drop the file from the file explorer onto the source window or you can browse for the file in the connection window. Then click onto Connect Datasource > Text Files > CSV File and browse for your CSV by clicking onto the ellipsis in the FileName field.

Then add the columns you want to create in the new table to the schema map and move onto the next step to configure and generate the script.

Generate the Script

In the schema window toolbar you can find the Generate SQL Script button. Click on this to open the configuration window.

You are first presented with a window to define the table name and select a connection to the Database you want to create the table in. If you want to run the script outside of Data Sync then you can leave this blank. You can also define the type of database and script you want to generate. These settings can also be changed later on.

You can only select connections saved in your connection library. If you have not saved your connection you will need to open a new Data Sync project, create and save the database connection to the connection library before you can connect in the SQL Script Generator.

The Script window will then be opened and you can now add additional columns and change column names, data types and data lengths. If you defined a key column in the schema map this will be identified as the primary key column in the configuration. If you need to set a composite key, you can do so here.

To generate the script click onto the Script Create Table in the window toolbar.

This will generate the script in a window below the columns.

You can make edits and additions to this script, copy it or run it directly from Data Sync. To run the script on the database connection you listed at the start click onto the Execute Query button.

Your table will now be created in the Database specified. If you refresh your connection library and navigate to your connection. You should now be able to see your table in the list.

Set the New Table as the Target

You most likely now want to synchronise data to the new table. To do this you need to connect your target to the table.

As this is stored in the connection library you can find your database connection, open the tables folder and either drag and drop or right click and select Set as Target.

Map the Source & Target Columns

Now check that the source and target columns all match and ensure you have selected your key column to identify each record.

Compare the Results and Sync

You can then click the Compare A > B button in the toolbar to preview the results. As this is a new table we will only see additions, but if you run the project again at a later date we may also see updates and deletions.

Deletes are disabled by default but will still show in the results. To enable deletes set EnableDelete to True on the target.

To synchronise the results click Synchronise and then Start to begin the sync.

Your data will then be visible in your new table. Make sure to save the final project so that you can run the future synchronisation again in the future.

Miscellaneous

Whilst using the SQL Table Script Generator you can edit the configuration you initially determined at the beginning. You can change the script language, change the name of the table to be created, and edit the database connection details.

Edit the Script Language

You can edit the language the script is generated by selecting between SQL Server, MySQL, and PostgreSQL in the drop down in the toolbar window.

Edit the Table Name

To edit the name of the table to be generated, and listed within the script, click onto the current name field in the toolbar of the window and simply make the changes you need.

Change the Database Connection Details

To change the database connection details you can click onto the connection in the script editor. This will open the Connection Properties for the connection you selected at the start. Make the changes you need and then click OK to save them.

Execute Selected Script

If you add additional script to the script generated by Data Sync, for example a SELECT query, then you can run just that script by highlighting it and then clicking Execute Query.

Taking the example we used above if I wanted to preview the records within that table after I had run the synchronisation I could use the query:

SELECT * FROM MyContact

Highlighting this query and clicking the Execute Script button will then bring up my data in the preview window below the script.

In the preview window you can also edit the records manually by double clicking onto the field you want to edit. Clicking out of that field will apply the changes.

If you are looking to run other SQL queries in Data Sync then you can do so using the SQL Query Tool. Check out our documentation for more information.