Skip to Content

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]

image

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.

image

Next the data is to be uploaded to an existing database table, here a table named CERTI is considered, which doesnot contain any records.

image

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.

image

Thus the data has been imported into the MaxDB Database. The data uploaded is shown below.

image

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.

image

The data from the table CERTI is exported to the file certi_backup.bat. The below file shows the data exported.

image

Thus, data can be easily imported and exported in MaxDB using sqlcli and batch files.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply