INSERT statement

With the menu item Table -> Paste SQL Statements -> INSERT INTO <tablename>…, SmartMYSQL creates a template of a MySQL statement with INSERT clause for the table selected in the Object Browser. The feature is useful to manually insert data in a table. just type the values for the respective columns in the template.

UPDATE statement

With the menu item Table -> Paste SQL Statements -> UPDATE <tablename> SET…, SmartMYSQL creates a template of a MySQL statement with UPDATE clause for the table selected in the Object Browser. The feature allows you to updates the datas in a table . just type the values for the respective columns in the template.

DELETE statement

With the menu item Table -> Paste SQL Statements -> DELETE FROM <tablename>…, SmartMYSQL creates a template of a MySQL statement with DELETE clause for the table selected in the Object Browser. The feature allows you to delete data in a table. just type the values for the respective columns in the template.

SELECT statement

With the menu item Table -> Paste SQL Statements -> Select <col-1> … <col-n> FROM…, SmartMYSQL creates a template of a MySQL statement with SELECT clause for the table selected in the Object Browser. The feature allows you to economize your efforts and avoid misprints in field names: all you have to do is only remove extra fields from the clause and introduce other changes to the query.

 

To copy table to different or same host by click on Table->Copy Table to different host  from object browser (or)  select Table->Copy tables to different host  (or)from This feature can be used instead of the Database->Copy DB to different Host  option when you need to copy a single table, but in fact you can use it for multiple tables at once.

Select the table you want to copy and then select Table -> Copy Table to different Host/Database.

At the appeared dialog box, make sure that the required table is default selected and if necessary, select other tables to be copied using treenode checkboxes. You may select the object(s) like tables, views, stored procs, functions, triggers and events to quickly select the object(s) of the same DB. Now select the target database where you want to copy the data.

Click Drop If Exists In Target to first delete the named object(s) in the destination database before copying them from the source database.

Click Structure Only to copy the selected object(s) with no rows (the destination table is empty) or click Structure AndData to copy the object(s) along with its data in the destination database.

Note: To specify a different host as the target database, you must first select Target connection from drop-down and then click on load  target database for selecting  the target database name.

 

 

To quickly view data of a table select the table in the Object Browser and select Open Table/Open Table in a new tab  from the popup menu

1. GRIDView
2. FORMView
3. TEXTView

There is a general LIMIT setting in ‘settings/preferences’, however, you can always change that value form the DATA tab itself. Note that it can take considerable time to fetch many rows of data from the server – depending on the server load, configuration and the quality of the connection. Also, the number of rows that can be displayed is limited by the memory available.

If by accident you have chosen to display so many rows that it takes too much time to load the data, there is a STOP option. The EXECUTE (single green arrow) – icon changes to a STOP icon while data is loading. Click this once and the loading of data will stop. Note that it may take a few seconds (depending on the connection) to communicate that message to the server!

To create a table, right click on the database name in the Object Browser and select the menu option Create Table from the popup menu. You can also use main menu: Database -> Create -> Table. This opens up a tab as shown below.

To create a table, right click on the database name in the Object Browser and select the menu option Create Table from the popup menu (or press INS). You can also use main menu: Database -> Create -> Table. This opens up a tab as shown below.

Create Columns

Insert your values in the Grid as shown below. Note that with different MySQL versions the display of this tab will vary a little. For instance, the ‘collation’ column is only displayed when connected to MySQL 4.1 (or higher) and starting from MySQL 5.x you can specify comments for table fields. Also, there is ‘Hide Language Options’ where charset and collation columns can be hidden for better viewability, which is MySQL version dependent. If the data entered is incorrect, you can clear all fields by clicking the Revert button. To create the table, click on the Save button.

Delete Columns

To drop a column in a table, you can either :

1. Select the column of a selected table in the Object Browser and select Others -> Columns-> Drop Column. A message box appears. Click on Yes to drop the column(s).
2. Right click on the table in the Object Browser and click on the Alter Table in the drop-down menu or press F6 to alter the highlighted table. Click on the check boxes beside the column name of the column(s) you wish to delete. Then click on the (-) sign in to toolbar(the delete button) to delete those columns.

If the columns are referenced by an index or foreign keys then a warning message will be displayed as shown below. On YES, it will modify the corresponding index(es) or foreign key(s).

 

To alter a table, right click on a table in the Object Browser and select the menu option Alter Table from the popup menu, or go to (Table -> Alter Table) from the menu bar (or press F6 if you have focus on that table in the Object Browser). This opens up a tab similar to the create table tab with the column names and values already filled up, as shown below. From here, edit the column value(s), add new column(s), delete existing column(s) etc.

If the table contains data rows, some data may be affected due to changes (i.e. if data type is changed or length is reduced). If the change is not permitted (e.g. you specify a column as primary key which contains NULL values), an error message is triggered and the specific offending change is not effected.

If the data entered is incorrect, you can cancel the changes by clicking the Revert button. Click on Save to alter the table. If you want to see the ALTER TABLE SQL-statement that SmartMYSQL sends to the server before executing it, click on the Preview tab.

To drop a column in a table, you can either :

1. Select the column of a selected table in the Object Browser and select Others -> Columns-> Drop Column. A message box appears. Click on Yes to drop the column(s).
2. Right click on the table in the Object Browser and click on Alter Table in the drop down menu or press F6 to alter the highlighted table. Click on the check boxes beside the column name of the column(s) you wish to delete. Then click on the (-) sign in to tool bar(the delete button) to delete those columns.

If the columns are referenced by any index or foreign keys then a warning message will  be displayed as shown below. On YES, it will modify the corresponding index(es) or foreign key(s).

Manage Columns

To change the sequence of columns in a table, select the table in the Object Browser and select Table -> More Table Operations ->Reorder Column(s) (Ctrl+Alt+R) (or) Others -> Columns-> Drop Column.

 from the popup menu. Select any column and click Up or Down button to move the column up / down. Having re-sequenced the columns, click Re-Order to save the table with changed structure. The column data in the table gets re-arranged accordingly.

 

Create Index

Click on the index subtab in the Create Table tab. If there is a Primary Key then that index would automatically be created.

Index Name: A name can be given for the index, but it is not necessary. A default name will be provided.

Columns: Select the column(s) from the drop down box on which you like to create the index.

To change the order of columns in the index, click on a column and click on the Up/Down buttons to move the column. Note that the order is applicable only for the Index. The column(s) in the table are NOT re-ordered.

Length: To index on a part of a column of Text data type (like Char, Text, Blob), enter the initial number of characters to be considered for Index in the length field. For example, when you enter 8, the index will be created on first 8 characters. If left blank, Index will be created on entire column. In case of BLOB data type, you must specify the length.

Index Type: Select index type from the drop-down. The index types are UNIQUE, PRIMARY, and FULLTEXT.

Click on Save to create the Index.

Alter Index

To alter an index, select a table from the Object Browser,

1) Click on “Manage Indexes” icon, or

2) Right click on the table(in the Object Browser) and select the menu option “Manage Indexes”, or

3) Press F7 key.

This opens up an Alter Table tab with the focus on Indexes sub tab.

  

Index Name: The Index Name is displayed. You can alter the Index Name.

Columns: Check/Uncheck the checkbox against the column name to include/exclude the column from the Index.

Click Save to make the changes. Or Click Revert to Cancel changes.

Create A Relationship

Select the Child table in the Object Browser and press F10 or select Relationships/Foreign Keys from the popup menu or click on the foreign keys subtab in the Create Table tab to create a foreign key.

Select the parent or the Reference Table from the combo box.

Specify the FK name and select the corresponding columns of the current Table. Each cell in the grid will pop up the corresponding columns for the table.

If you want to create an FK on multiple columns just select those columns from the dialog box. Also select referenced database, referenced table and referenced columns. After you are done, press Save.

Note: If you want to set rules for On Delete or On Update operations then you can do it from here.

Delete A Relationship

To delete an FK, select a table from the Object Browser,

1) Click on “Relationships/Foreign Keys” icon, or

2) Right click on the table(in the Object Browser) and select the menu option “Relationships/Foreign Keys”, or

3) Press F10 key.

This opens up an Alter Table tab with the focus on Foreign Keys sub tab.

Click on Delete button in the toolbar to delete the selected relationship.

Before MySQL/InnoDB-4.0.13, MySQL does not provide you with a one-query-method to delete a FK. MySQL and InnoDB docs suggest dropping the table and recreating the table without the relationship. If you have tables with lots of columns and data, this process really becomes tedious.

However, SmartMYSQL let you overcome this pain, and does all the steps internally. You are just required to select Delete.  If you want to delete an FK, just select the relationship from the Grid and press Delete.

 

SmartMYSQL treats one of the tables in the relationship as the child and the other parent. Using SmartMYSQL you create relationships on the child table that will refer to the columns of the parent table.

First of all you need to create correct indexes on the columns of both the tables. You can do this from the Index tab.

Select the Child table in the Object Browser and press F10 or select Relationships/Foreign Keys from the popup menu or click on the foreign keys subtab in the Create Table tab.

 

Rename Table

To rename a table name select the table in the Object Browser and select Table ->  Operations -> Rename Table from the popup menu or press F2).

Empty Table

To delete all rows select the table in the Object Browser, right-click and select Table ->  Operations -> Truncate Table (or press Shift+Delete). This option is also available in ‘Tools’ menu.

Select the Table to be emptied in the explorer window. You get a warning message box. Click Yes to truncate all data in the Table (but the table is NOT dropped from the database).

You get a message if the truncation is not permissible (e.g. attempting to drop rows with foreign key values)

Drop Table

Select the table you want to drop in the Object Browser and select Table ->   Operations -> Drop Table from Database from the popup menu (or press Delete).

Reorder Columns

To change the sequence of columns in a table, select the table in the Object Browser and select Table -> More Table Operations ->Reorder Column(s) (Ctrl+Alt+R) from the popup menu. Select any column and click Up or Down button to move the column up / down. Having re-sequenced the columns, click Re-Order to save the table with changed structure. The column data in the table gets re-arranged accordingly.

Change Table Type

To change the Table type (or “Engine’ as the term is for the latest MySQL versions), you select the Table Type from the drop-down list you would like to change the selected table too. SmartMYSQL queries (‘asks’) the server about what types/engines are available and only displays the ones available (that may vary considerably depending on the server version, the platform where the server runs and how it was compiled and how it is configured).

Table  Properties

To view  the Advanced Properties of a table, select the table in the Object Browser

and select Table -> More Table Operations -> View Table  Properties from the popup menu.

To define or to edit the Advanced Properties of a table click the ‘Advanced Properties’ button from CREATE TABLE or ALTER TABLE.

The properties displayed are as they were defined with the CREATE TABLE or latest ALTER TABLE statement. Note that not every setting for every property has effect with every storage engine. However it is stored what you define and whenever you change the table to an engine/type that supports some defined option, that option will take effect if not already the case.

 

Scheduled Backups

This option is available from Powertools -> Scheduled Backups….From Table->Backup/Export->Scheduled Backups from Menu by pressing Ctrl+ALT+S

SmartMYSQL’s Scheduled Backup Wizard allows you to schedule Exporting of Data as SQL Dumps. You can use this wizard to export data into a single file or as multiple files.

Starting Scheduled Database Backups

Startup Scheduled Backup Wizard found in the Powertools menu or use keyboard shortcut Ctrl+Alt+S to start the tool. The first screen you will get is:

This page allows you start up a backup session or to edit an existing session.

Select Start a new Session and click Next to start a new backup session.

In this page you need to provide details about your MySQL database.

There are also are two different options to backup ALL databases (but it will of course only backup the databases that this user can access on the server). One of those options includes the `mysql` database and the other does not. You should be careful about backing up and restoring the `mysql` database unless you are the server administrator. For most users best option is to backup ‘ALL user data’ (without `mysql` database). Note that both ALL options will not backup the Information_Schema database and Performance_Schema. These databases are read-only databases and cannot be restored. However, if you want, you may select it individually for export.

Note that it’s not required to have SmartMYSQL currently connected to any database to execute the operation. The session establishes all required connections during the operation execution.

Selecting object(s) that you want to export

In this page, you choose whether you want to create backup of all the objects or selected object(s).

Select Export All Objects to export all the objects. If you want to export only selected object(s) then select Export Selected Object(s) and select the corresponding object(s) in the listbox.

Here in the dialog box, first select the object(s) like tables, views, stored procs, functions, triggers and events to be copied from the source database. You may Select All / Deselect All by clicking on tree node checkboxes to select the object(s) quickly.

How do you want to generate the script

SmartMYSQL allows you make a complete dump in one single dump file or across separate files in a directory. If you choose to export to separate files then SmartMYSQL will generate obj_{objectname}.sql in the folder specified.

Select the option and provide the corresponding File name or Folder name accordingly.

You can even tell SmartMYSQL to prefix the specified file name with the current timestamp and configure it to overwrite or append to existing script files.

Compress backup files: When this option is checked SmartMYSQL will create a zip-archive of the backup.

Options

The next two pages of the wizard allow you to set various options for the backup process. SmartMYSQL provides you with maximum flexibility and options to generate scripts as you want.

Structure and Data: Select this option if you want to backup both structure and data information for a table.

Structure Only: Select this option if you want to backup only structure information for a table.

DDL Options:

— Include “CREATE database” statement: Select this option if you want to add “Create database” statement in the output.

— Include “USE database” statement: Select this option if you want to add “Use database” statement in the output.

— Include “DROP” statement: Select this option if you want to add “Drop Object(s)” statement in the output.

— Ignore Definer: It is an option to Ignore DEFINER clause for database objects. Note that the target server will then create DEFINER as current SmartMYSQL user when creating the object.

DML Options:

— Generate Bulk INSERT statement: When this option is checked data from more rows will be written into a single INSERT statement. Each INSERT statement will be allowed to grow to the size specified in the ‘Max. size of BULK INSERTS’ setting.

— Generate complete INSERT statement: Select this option if you want to backup data of a table as complete insert statements. Checking this option will generate SQL statements like: insert into tablename ( col1, col2, col3 ) values ( 1, 2, 3 ). Keeping the option unchecked will result in SQL statement being generated as: insert into tablename values ( 1, 2, 3 ).

— Generate INSERT DELAYED statement: Select this option if you want to generate INSERT DELAYED statements. For more information on INSERT DELAYED

Note that to ensure the integrity of your final export result; most of the options in this section are disabled if at least one of the DB’s tables was not selected for export at the previous stages of the process. For more info Backup Database as Sqldump.

This window allows you to set various other options while exporting data.

FLUSH Options:

• Flush Logs before dump:Using this option will ensure that all data changes pending will been written to disk before the backup starts.
• Flush master log and Flush slave logs:Leave them unchecked if the server is not a replicating master or slave. An attempt to FLUSH either if it does not exists will return an error and will/may abort the job

Options having  an effect on source:

• LOCK All Tables for read:The LOCK will be generated for one table (the one for which INSERT statements is currently generated) at a time.
• Single Transaction: Option (similar to ‘mysqldump’ “-–single-transaction”) has only effect with Transactional storage engines (like InnoDB). All tables will be backed up from their state at the time the backup started. This option will ensure consistency across tables with Foreign Key CONSTRAINTs. The Single transaction option and the LOCK ALL Tables option are mutually exclusive.

Options written to file:

• Set FOREIGN_KEY_CHECKS=0: This option should always be checked if tables with Foreign Keys are backed up as there is no way to ensure that a ‘parent’ table is written to the file before a ‘child table’. If they are written in opposite order restore will fail if the option is not selected.
• Add Lock around INSERT statement(s): This will ensure that no other clients have WRITE access to tables during restore as long as they are not fully restored.
• Add DISABLE/ENABLE keys around INSERT statement(s): If this option is checked the server will not maintain indexes during the restore process (while executing INSERT statements) but generate indexes for each table when the restore of the table is complete. This may result in performance improvements during restore.
• size (in KB) for BULK INSERT statement(s): Here user may specify how large BULK INSERTS are allowed to be. Note: Any specification larger than 16MB will ’rounded down’ to 16 MB. The size should be specified not larger than the max_allowed_packet setting on the server where you want to restore the dump. If you try to execute BULK INSERT statement(s) larger than the server setting the server will disconnect the client and you will get the error ‘The MySQL Server has gone away’.
• Chunk size (in rows) during batch exports:The Chunk setting specifies how many rows will be fetched for every SELECT statement sent when retrieving data for generation of SELECT statements.  Default is 1000. With very ‘wide’ and tables with large BLOB/TEXT columns you may get better performance with a lower setting – in particular when HTTP tunneling to a shared MySQL server where there may be a restriction on the memory a user is allowed to use.

Error Handling

This window allows you to configure SmartMYSQL about how to handle situations when an error occurs.

Abort on error: If you select this option then SmartMYSQL will stop the export process as soon as it faces an error.

Email notifications: SmartMYSQL’s Scheduled Backup has an option where it can send you report of the export process over mail. You can configure SmartMYSQL to send you the report only if an error occurs or every time the process executes.

To setup SmartMYSQL to send notifications over email, just select the option Email Notifications and press Next. In the next window as shown below, provide your SMTP server details to send you email.

 

Executing a Job

Selecting Run immediately starts up SJA as a child process to execute the query(s) and

send the resultset(s) over email, if requested. The complete detail of the process is shown in the last step of the wizard.

Check the option Saving Options if you need to use the job repeatedly.

• Select Save Job File if in the future you suppose to run the job manually (for this select the option Edit a saved Session at the Wizard’s first page or use the Job File as a command_line argument for SJA.exe). With this option, at the following page of the Wizard you should indicate the name for the Job File which will be saved in the XML format.
• Select Windows Schedule if in the future you need the job to be launched automatically according to a certain schedule. In this case at the following page of the Wizard you should indicate the name for the Job File which will be saved in the XML format and the name for a MS Windows Schedule which will be automatically created for you by SmartMYSQL as soon as the job is saved. The Properties window will appear and you will be able to schedule it as necessary.
• In the box at the bottom of the window, specify the folder path where the SmartMYSQL Log file must be saved.

Finishing Up The Scheduled Export Wizard

Selecting Run immediately commands SmartMYSQL to launch the export session and show you the Wizard’s log page where you can monitor the process.

Selecting Save Configuration file brings up the below dialog. Specify the file name and press Finish; SmartMYSQL will then save your session.

Selecting Save & Schedule It using Windows Scheduler you will get the below dialog. You need to save the session details in an  XML file before you can schedule it. Clicking on Finish button starts up the Windows Task Scheduler dialog.

Backup Table As SQL Dump

You can dump a database for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump will contain SQL statements to create the table and/or populate the table. To generate a dump select the database or table in the Object Browser and select Database -> Backup/Export -> Backup Database As SQL Dump… This option is also available in Table -> Backup/Export -> Backup … or just press Ctrl+Alt+E.

Database name: Select the source Database from the list of available databases.

Export as SQL: Specify the required option depending on what you need to export: only the database structure, only the data or both.

Export to file: Specify the Export file name.

Now check the following options as required by you:

Options having an effect on source:

• Lock all tables for read: The LOCK will be generated for one table (the one for which INSERT statements is currently generated) at a time.
• Flush logs before dump:Using this option will ensure that all data changes pending will be written to disk before the backup starts.
• Single transaction: Option (similar to ‘mysqldump’ “-?–single-transaction”) has only effect with Transactional storage engines (like InnoDB). All tables will be backed up from their state at the time the backup started. This option will ensure consistency across tables with Foreign Key CONSTRAINTs. The Single transaction option and the LOCK ALL Tables option are mutually exclusive.

Options written to file:

• Include “USE database” statement: To insert the USE database name in the script.
• Include “CREATE database” statement: To insert the CREATE DATABASE in the script.
• Set FOREIGN_KEY_CHECKS=0: This option should always be checked if tables with Foreign Keys are backed up as there is no way to ensure that a ‘parent’ table is written to the file before a ‘child table’. If they are written in opposite order restore will fail if the option is not selected.
• Add Lock Around INSERT statement(s): This will ensure that no other clients have WRITE access to tables during restore as long as they are not fully restored.
• Create Bulk Insert statement(s): When this option is checked data from more rows will be written into a single INSERT statement. Each INSERT statement will be allowed to grow to the size specified in the ‘Max. size of BULK INSERTS’ setting.
• Include “DROP” statement(s): Inserts DROP statement(s) in the script to drop the named object(s) first before restoring the script. The option does have an effect on the database – only tables and other objects (Views, ‘Stored Programs’).
• Ignore DEFINER: It is an option to Ignore DEFINER clause for database objects. Note that the target server will then create DEFINER as current SmartMYSQL user when creating the object..

Advanced Options:

• Prefix with Timestamp: This will ensure that the timestamp i.e. date and time of the backup is added to the name of the .sql file created.
• File per Object: This option allows you to backup data multiple files across SQL files. SmartMYSQLwill generates a file named tbl_(file_name).sql for each file.
  If both are selected, then a folder will be created by the name of the file with the timestamp in the name of that folder and each of the files inside that folder will have the timestamp as the prefix in the name.

In SQL Dump dialog box, first select the object(s) like tables, views, stored procs, functions, triggers and events to be backed up from the source database. You may Select All/Deselect All by clicking on tree node checkboxes to select the object(s) quickly. Now select the file where you want to export the data.

Click Export to create the script (batch) file. SmartMYSQL does the export in a different thread so you can stop the export process anytime.

EXPORT BASIC

Table-> Export (CTRL+ALT+C)

With SmartMYSQL you can export data of a table you can select the table in the Object Browser and by right clicking select Backup/Export. Here we have 5 options,

• Backup Database As SQL Dump…
• Export basic
• Export Advanced
• Export Table Data As XLS
• Export Table Data As PDF

Export Basic:

Right click on table-> Backup/Export -> Export Basic. This option is also available in menu Table -> Backup/Export -> Export Basic.

Now you can export data of a table in various formats. The available options are:

• HTML
• TEXT
• MS Excel
• PDF
• JSON
• XML
• CSV
• SQL

Also, you can export the resultset of a query in the same formats. By using Export basic option you can export entire table. If you want to select particular columns to include in the export you can use Export Advanced option.In every case this dialogue will appear.

The relevant section of this dialogue becomes active as you select your option. We will now have a detailed look on the different export options:

As CSV:

If you want to change the program defaults (or whatever you entered last time you use this function) Click the Change and in the appearing dialogue check options as you need and specify a file name for the export.

To fully understand this dialog you will need to understand the MySQL “LOAD DATA INFILE” statement. However, there is an extremely useful feature if you wish to copy your data from the Clipboard directly to an Excel workbook or to a file which will be then opened in MS Excel. Use the button Change -> Specify the escape characters pops and now click on Fill Excel friendly values where SmartMYSQL will automatically select the delimiters used in Excel. Note that to use with Windows localizations distributed in most continental European countries you should consider change the ” , ” (comma) to ” ; ” (semicolon), as quite a lot of localized programs expect that (true for Microsoft Excel and the Microsoft text-ODBC driver for instance).

But you can of course specify any other delimiters if you need and technical details for rows (Escape Characters and Line Terminator Characters) and fields (Fixed Length, if used, or Terminators and Enclosers, if field length is Variable). Here you can also specify if Column Names must be copied with the data.

After filling in all the details, press Export in the Export Data as CSV dialog window.

 

Sample Output CSV

As Excel:

The Excel data format we support is the XML-based format introduced with Microsoft Office 2003. It also works on Office 2007 (only small changes that do not affect this functionality). With use with previous versions, there is a ‘Compatibility pack’ for Excel 2000 and Excel 2002/XP available as a download from the Microsoft webpage.

Next image shows SmartMYSQL connected to MySQL with a database containing a broad variety of DATE and TIME type variables you will see some ‘weird’ values but they are all valid with the MySQL version in question and the server sql_mode:

Sample output as excel

Export Advanced:

Select 3rd option Backup/Export -> Export Advanced. This option is also available in menu Table -> Backup/Export -> Export Advanced.

Now you can export data of a table in various formats. The available options are:

• CSV
• HTML
• Excel
• XML
• PDF
• Delimiter file
• SQL (NOTE:This saves the data in most simple form of  SQL statements.That this ‘export data as SQL’ is not intended for backup purposes. Read the full explanation at the bottom of this page!)

Also, you can export the resultset of a query in the same formats. You can also select what columns to include in the export. To access this feature you can

• right-click the GRIDs of DATA or RESULT tabs,
• right-click a table in the Object Browser.
• select the option from ‘Table’ or ‘Tools’ menu. From ‘Tools’ menu it will export what tab is active (the DATA tab or a RESULT tab)

In every case this dialogue will appear:

 

The relevant section of this dialogue becomes active as you select your option. We will now have a detailed look on the different export options:

As CSV:

If you want to change the program defaults (or whatever you entered last time you use this function) Click the Change and in the appearing dialogue check options as you need and specify a file name for the export.

To fully understand this dialog you will need to understand the MySQL “LOAD DATA INFILE” statement. However, there is an extremely useful feature if you wish to copy your data from the Clipboard directly to an Excel workbook or to a file which will be then opened in MS Excel. Use the button Change -> Specify the escape characters pops and now click on Fill Excel friendly values where SmartMYSQL will automatically select the delimiters used in Excel. Note that to use with Windows localizations distributed in most continental European countries you should consider change the ” , ” (comma) to ” ; ” (semicolon), as quite a lot of localized programs expect that (true for Microsoft Excel and the Microsoft text-ODBC driver for instance).

But you can of course specify any other delimiters if you need and technical details for rows (Escape Characters and Line Terminator Characters) and fields (Fixed Length, if used, or Terminators and Enclosers, if field length is Variable). Here you can also specify if Column Names must be copied with the data.

After filling in all the details, press Export in the Export Data as CSV dialog window.

Sample Output CSV

As Excel:

The Excel data format we support is the XML-based format introduced with Microsoft Office 2003. It also works on Office 2007 (only small changes that does not affect this functionality). With use with previous versions there is a ‘Compatibility pack’ for Excel 2000 and Excel 2002/XP available as a download from the Microsoft webpage.

Next image shows SmartMYSQL connected to MySQL with a database containing a broad variety of DATE and TIME type variables you will see some ‘weird’ values but they are all valid with the MySQL version in question and the server sql_mode:

After export using the Excel option it displays like this in Excel 2003

Sample Excel Output

As SQL Statements:

With ‘Export as SQL’ a single-table/result set can be exported. The purpose of this export option is to provide a simple and fast way to export data embedded in SQL INSERT statements. With this tool you can also export data embedded in INSERT statements from a resultset from a query using a JOIN or UNION. However you should observe that if two or more JOINED tables have identically named columns the data will not import unless you use an ALIAS for at least one of them.

Also a very simple ‘CREATE STATEMENT for the TABLE’ is created (if you choose), but this create statement is not a complete CREATE statements with all table and column options (such as Character Set and Storage Engine settings) and it should not be used for backup purposes. ‘powertool’ of SmartMYSQL Enterprise. Those are the two options we provide that are ‘fit’ for backup purposes and other situations where you want detailed information about table structure(s).

If you want to use a text file with SQL statements for Backup purposes you should not use this option, but use the ‘Backup database as SQL Dump’ option instead.

‘Export as SQL’ will be enabled only if there is a single table associated with the Result tab. For a multi-table result sets even selecting a table from drop-down, ‘Export as SQL’ will be disabled.

Export Table Data As XLS:

To export table data into A file, select the table in object browser  and select Table ->Backup/Export-> Export Table Data As XLS.

Columns: The list of all Columns of the selected Table is shown. Select the Columns for which the data to be imported from the File (you should be careful to specify the columns else you may get error or wrong result.

 

Export Table Data As PDF:

To export table data into A file, select the table in object browser and select Table ->Backup/Export-> Export Table Data As PDF.

Columns: The list of all Columns of the selected Table is shown. Select the Columns for which the data to be imported from the File (you should be careful to specify the columns else you may get error or wrong result.

 

 

Import External Data

To import data from external  files into a MySQL table, select the table in object browser and select Table -> Import -> Import External data or(Ctrl+Alt+O).

Import from Files: Enter the name of the file (or click the … button to get the File Selection dialogue box to select the file .

Databases: The list of all Databases is shown. Select the Database name for which the data to be imported from the File.

Tables: The list of all tables of the currently active database is shown. Select the Table from the list box.

Choose Imported Data Type:By click on options provided in the wizard, we can choose the type of imported file format.SmartMySQL provides the following imported options.

•  CSV
• HTML
• Excel
• XML
• Delimiter
• SQL

Import CSV Data Using LOAD LOCAL

To import data from a CSV file into a MySQL table, select the table in object browser and select Table -> Import -> Import CSV Data Using Load Local… or(Ctrl+Shift+M).

Tables: The list of all tables of the currently active database is shown. Select the Table from the list box.

Columns: The list of all Columns of the selected Table is shown. Select the Columns for which the data to be imported from the File (you should be careful to specify the columns else you may get error or wrong result).

Import from Files: Enter the name of the file (or click the … button to get the File Selection dialogue box to create/select file) from which the data in CSV format would be imported.

Other options:

Low Priority: The transfer job is carried only when idle resources are available

Concurrent: the transfer job is carried along with other processes (which are not of high priority).

Replace: Replaces the data in target MySQL table of same primary key value.

Ignore: Skips the row of same primary key value in the MySQL target table.

Ignore lines: Starts reading data in the CSV file after skipping specified number of lines from the top in the CSV file. Useful to skip header (or a bunch of records from the top).