Import and Export Data in MaxDB using SQLCLI and Batch files
Importing and exporting data in a database is a most common task. MaxDB which comes with a set of powerful tools provides many possible ways to perform the same. When it comes to data that are present in flat files, then MaxDB Loader is a very helpful tool that can be used to import and export data. It has a rich set of commands like DATALOAD, FASTLOAD..etc, that make the entire process very much simplified.
MaxDB SQLCLI is another tool that help in functions like data manipulation, data definition etc. This weblog discussess the importing and exporting of data into MaxDB with the help of batch files. The notable point is that SQLCLI has an added advantage of processing the batch files with built in commands like the Loader. Thus it is possible to run many simple queries and also exporting the results to flat files.
Logging into MaxDB Database instance
The users with a login can access the database instance using their user name and password. The command that will connect the user to the database instance is given below,
cmd > sqlcli -u user_name,user_password :- This gets you connected to the database server,
sqlcli => \c -d database_name :- Gets the access the required database.[\c – command to create a connection]
sqlcli [options] -d database_name -u database_user,database_user_password can be used for logging with additional functionalities.
Creating a Batch file for SQLCLI
When more than one query has to be executed in batch mode then, then a separator ” -c “has to be used whose value is // . This instructs the sqlcli that more than one query is present. Further, when data has to be inserted, some set of insert queries can be framed. But for mass importing of data then loader can be prefered.
Next the data is to be uploaded to an existing database table, here a table named CERTI is considered, which doesnot contain any records.
Importing Data into the Database
The batch file that has been created is passed as input in the command line. The command ” \i file_and_path “ can be used to specify the batch file to the sqlcli, where ” i “ denotes input. On giving the command, the batch file is read and all commands are executed one by one. The status of the executed command is displayed in the command prompt with appropiate messages.
Thus the data has been imported into the MaxDB Database. The data uploaded is shown below.
Exporting Data to flat files
The data exporting can be performed directly using the MaxDB loader that has the command already built in. But when coming to sqlcli, the same can be performed by combining a couple of simple queries.The command ” \o file_and_path “ can be used to specify the batch file to the sqlcli, where ” o “ denotes output. Here, the set of queries that follow the \o command are executed and the results are exported to the file specified. The operation can be terminated using the exit command.
The data from the table CERTI is exported to the file certi_backup.bat. The below file shows the data exported.
Thus, data can be easily imported and exported in MaxDB using sqlcli and batch files.