Simple SQL Merge with Excel data..


SQL proficient users will be pleased to know you can modify the SQL generated as you require.

By default the application will create insert or stored procedure statements for you such as this example:



As you can see the 'values' section contains a set of Excel merge codes, each representing a column from the spreadsheet.

The script is standard SQL as compatible with your destination database (Access in the above example), the utility will automatically format the SQL as required by the database (SQL Server/MySQL etc)


Custom SQL
[img]
You can of course modify the SQL as you wish and use the merge codes wherever you would like within the code.

The system will merge the SQL with Excel data for each row you have selected for validation or send to datatbase.

Testing the Merge Script
The button 'SQL Merge Script' will merge your SQL with the data from the spreadsheet and display the generated SQL in notepad.

A good way to test the validity of your script is to use the Validate button in the main application, select two or three rows and click Validate. The system will run the merged SQL within a transaction, and will always roll the transaction back when validating so no data will be affected by the test.

Any problems/errors with the merged SQL will be displayed in the first column.

INSERT or UPDATE
A common challenge when uploading data is to decide to INSERT or UPDATE depending on data already existing.

We will create a new tip 'INSERT or UPDATE' to cover this topic.

Hope you found this tip useful.

Edited by user Wednesday, August 01, 2012 10:52:35 PM(UTC)  | Reason: Not specified