Here you can find the detailed instruction on how to export data from SQLite database to SQL file(s) in Exportizer sofware. Exporting to SQL files from databases of other types like SQL Server, Oracle, PostgreSQL, MySQL etc is very similar; the difference is in registering the database in Exportizer.

Below, it is shown how to export data from GUI or command line.

You can export data directly to your target database using Exportizer Pro or Exportizer Enterprise, without needing to produce intermediate SQL files. But anyway, if you have a reliable SQL tool which can execute your SQL script in the target database, Exportizer can help to prepare such a script.
Please note, that when choosing exporting to SQL, you do the things twice: first, export the data to SQL file(s); second, load data from the script(s) to your destination database. Therefore, this option can be less effective, especially for large datasets or when automating the data exporting.

Export Conditions

To export from SQLite to SQL file, please consider the following:

  • SQLite3.dll must be accessible.
  • You can open SQLite database either by Exportizer Pro or by Exportizer Enterprise. If you have created an ODBC DSN for your SQLite database, Exportizer Pro can be used, otherwise Exportizer Enterprise should be used.
  • When using ODBC on the source side, make sure the Exportizer and the corresponding SQLite ODBC driver have the same architecture (32-bit or 64-bit).

Note: If your operating system is 64-bit, you can install both Exportizer 32-bit and Exportizer 64-bit and use them independently.

Configuring SQLite to SQL File(s) Exporting

If you are exporting data via GUI:

  1. Launch Exportizer Pro or Exportizer Enterprise.
  2. Register your source SQLite database:
    • In Exportizer Pro, use ADO interface. The database must be an ODBC DSN, pointed to your SQLite database file.
    • In Exportizer Enterprise, use FD interface. In most cases, specifying a pointer to the SQLite file is enough. Sometimes, specifying the Vendor library parameter may be needed: this must be a main DLL from the folder where SQLite installed, e.g. sqlite3.dll.

Export Steps (GUI)

  1. Open the registered SQLite database.
  2. Choose a table or tables to export or write and execute your SQL query or queries. Please note that exporting multiple datasets at a time is available in Exportizer Enterprise only.
  3. Click Export button .
  4. Switch to the SQL Script tab.
  5. If you exporting a single dataset (table or SQL query), specify a destination SQL file. When exporting multiple datasets, you have a choice:
    • export data to multiple files in one destination folder, one dataset per file (default option);
    • export all datasets to one file, using Folder/File drop-down list .
  6. Specify export parameters: target table name, statement type, target database type, file encoding and so on.

    Statement type specifies the type of SQL commands to produce. The following commands are supported:

    • INSERT
    • BATCH INSERT (or BULK INSERT) (if supported by target database type, e.g. Oracle or PostgreSQL)
    • UPDATE
    • MERGE (the combination of INSERT and UPDATE)
    • DELETE
    • PostgreSQL Copy (for PostgreSQL only)

    Target database type specifies the type of database where the generated SQL command will be executed. For example, SQL Server, Firebird, SQLite etc.

    There is a block of options to specify initial actions. These are SQL commands which must be added before the data processing statements. For example, if you want to drop existing table and then create it again before inserting records, use Drop table and Add DDL options; if you are not sure if the target table exists, specify Add IF (NOT) EXISTS option (if supported by the target database type, e.g. PostgreSQL or SQLite). Please note that some initial actions are mutually exclusive, for example, you cannot use Empty table and Drop table options at the same time.

    Exporting to SQL Script
  7. Click Next.

    Specify the source-to-target table and/or field mappings.

    In field mappings, you create the correspondence between the source and target columns. In addition to source table columns, you can also use calculated fields specified by formulas.

    In table mappings (for multi-table exporting), you create the correspondence between the source datasets (tables and/or queries) and target SQL files. You can specify target table name and target file for each exported dataset. You can also specify nested field mappings for each table pair.

  8. Click Export.

Exporting SQLite to SQL File(s) Using Command Line

Command Line Examples

1. Exporting a table from SQLite file (using settings you see on the image above):

"C:\Program Files (x86)\Vitalii Levchenko\Exportizer Pro 9\exptizer.exe" /export /ExportType=SQL /ExportMode=REPLACE+INSERT /SuppressOverwriteOrDeletePrompt /FieldMappings=ID=id;FIRST_NAME=first_name;LAST_NAME=last_name;CITY=city;COUNTRY=country /TrgTableDescription=Customers /sqlSeparator=; /sqlTableName=public.customer /TargetDBType=POSTGRESQL /AddDDL /DDLAddCheckExistence /DropTable /sqlStatementType=BATCHINSERT /CommitInterval=100 /encoding=UTF-8 /TrimTrailingSpaces /LineTerminator=WINDOWS /IncludeMemo /SrcDBInterface=fd /SrcDBKind=FILE /SrcDBDriver=SQLITE /SrcDB=C:\MyData\TestSQLiteDb.db /SrcSharedCache=True /SrcLockingMode=Exclusive /SrcTableName=customer /TrgDB=C:\MyData\customer.sql

2. What is bad in the previous example, is poor representing of the source-to-target filed mappings. Preparing a separate file for the field mappings gives us much more possibilities like specifying a primary key and full SQL specifications for target columns, and adding column descriptions (if supported by target database). Instead, we can prepare (it can be easily done from GUI in Export dialog) a field mappings file, and the use it in the command line. So, in the next example, we have replaced /FieldMappings parameter with /FieldMappingsFile parameter:

"C:\Program Files (x86)\Vitalii Levchenko\Exportizer Pro 9\exptizer.exe" /export /ExportType=SQL /ExportMode=REPLACE+INSERT /SuppressOverwriteOrDeletePrompt /FieldMappingsFile=C:\MyData\Customer_FieldMappings.xml /TrgTableDescription=Customers /sqlSeparator=; /sqlTableName=public.customer /TargetDBType=POSTGRESQL /AddDDL /DDLAddCheckExistence /DropTable /sqlStatementType=BATCHINSERT /CommitInterval=100 /encoding=UTF-8 /TrimTrailingSpaces /LineTerminator=WINDOWS /IncludeMemo /SrcDBInterface=fd /SrcDBKind=FILE /SrcDBDriver=SQLITE /SrcDB=C:\MyData\TestSQLiteDb.db /SrcSharedCache=True /SrcLockingMode=Exclusive /SrcTableName=customer /TrgDB=C:\MyData\customer.sql

Here is what can be inside the field mappings file:

<?xml version="1.0" encoding="UTF-8"?> <FieldMappings> <Software>Exportizer Pro</Software> <SoftwareVersion>9.2.2.98</SoftwareVersion> <KeyFields></KeyFields> <Items> <FieldMapping> <Source> <FieldName>ID</FieldName> </Source> <Target> <FieldName>id</FieldName> <FieldType>INTEGER</FieldType> <FieldSize>-1</FieldSize> <FieldScale>-1</FieldScale> <FieldNotNull>1</FieldNotNull> <FieldDefaultValue></FieldDefaultValue> <FieldDescription>Customer Id</FieldDescription> <PrimaryKey>1</PrimaryKey> <FieldSQLSpecification>INTEGER NOT NULL</FieldSQLSpecification> </Target> </FieldMapping> <FieldMapping> <Source> <FieldName>FIRST_NAME</FieldName> </Source> <Target> <FieldName>first_name</FieldName> <FieldType>VARCHAR</FieldType> <FieldSize>12</FieldSize> <FieldScale>-1</FieldScale> <FieldNotNull>1</FieldNotNull> <FieldDefaultValue></FieldDefaultValue> <FieldDescription>First name</FieldDescription> <PrimaryKey>0</PrimaryKey> <FieldSQLSpecification>VARCHAR(12) NOT NULL</FieldSQLSpecification> </Target> </FieldMapping> <FieldMapping> <Source> <FieldName>LAST_NAME</FieldName> </Source> <Target> <FieldName>last_name</FieldName> <FieldType>VARCHAR</FieldType> <FieldSize>16</FieldSize> <FieldScale>-1</FieldScale> <FieldNotNull>1</FieldNotNull> <FieldDefaultValue></FieldDefaultValue> <FieldDescription>Last name</FieldDescription> <PrimaryKey>0</PrimaryKey> <FieldSQLSpecification>VARCHAR(16) NOT NULL</FieldSQLSpecification> </Target> </FieldMapping> <FieldMapping> <Source> <FieldName>CITY</FieldName> </Source> <Target> <FieldName>city</FieldName> <FieldType>VARCHAR</FieldType> <FieldSize>16</FieldSize> <FieldScale>-1</FieldScale> <FieldNotNull>0</FieldNotNull> <FieldDefaultValue></FieldDefaultValue> <FieldDescription>Customer city</FieldDescription> <PrimaryKey>0</PrimaryKey> <FieldSQLSpecification>VARCHAR(16)</FieldSQLSpecification> </Target> </FieldMapping> <FieldMapping> <Source> <FieldName>COUNTRY</FieldName> </Source> <Target> <FieldName>country</FieldName> <FieldType>VARCHAR</FieldType> <FieldSize>16</FieldSize> <FieldScale>-1</FieldScale> <FieldNotNull>0</FieldNotNull> <FieldDefaultValue></FieldDefaultValue> <FieldDescription>Customer country</FieldDescription> <PrimaryKey>0</PrimaryKey> <FieldSQLSpecification>VARCHAR(16)</FieldSQLSpecification> </Target> </FieldMapping> </Items> </FieldMappings>

And here is what we can get in the target SQL file after executing the command line above:

DROP TABLE IF EXISTS public.customer; CREATE TABLE IF NOT EXISTS public.customer ( id INTEGER NOT NULL, first_name VARCHAR(12) NOT NULL, last_name VARCHAR(16) NOT NULL, city VARCHAR(16), country VARCHAR(16), PRIMARY KEY (id) ); COMMENT ON TABLE public.customer IS 'Customers'; COMMENT ON COLUMN public.customer.id IS 'Customer Id'; COMMENT ON COLUMN public.customer.first_name IS 'First name'; COMMENT ON COLUMN public.customer.last_name IS 'Last name'; COMMENT ON COLUMN public.customer.city IS 'Customer city'; COMMENT ON COLUMN public.customer.country IS 'Customer country'; INSERT INTO public.customer(id, first_name, last_name, city, country) VALUES (1, 'Sandra', 'Bush', 'Portland', 'United States'), (2, 'Eric', 'Miles', 'Edmonton', 'Canada'), (3, 'Berndt', 'Mann', 'Hamburg', 'Germany'), (4, 'Marek', 'Przybylsky', 'Krakow', 'Poland'), (5, 'John', 'Hladni', 'Bedford', 'United States'), (6, 'Bogdan', 'Vovchenko', 'Kyiv', 'Ukraine'), (7, 'Paul', 'Vogel', 'Hamburg', 'Germany'), (8, 'Ion', 'Rotaru', 'Kishinev', 'Moldova'), (9, 'Mehmed', 'Rabbani', 'Liege', 'Belgium'), (10, 'Rita', 'Hagen', 'Vienna', 'Austria'), (11, 'Andreas', 'Muller', 'Berlin', 'Germany'), (12, 'Hans', 'Petersen', 'Copenhagen', 'Denmark'), (13, 'Shimon', 'Rabinovich', 'Tel Aviv', 'Israel'), (14, 'Rick', 'Yonley', 'Miami', 'United States'), (15, 'Jose', 'Pereira', 'Madrid', 'Spain'), (16, 'Antonio', 'Lippi', 'Naples', 'Italy'), (17, 'Jean-Paul', 'Papin', 'Paris', 'France'), (18, 'Lars', 'Johansson', 'Stockholm', 'Sweden'), (19, 'Stephen', 'Williams', 'London', 'United Kingdom'), (20, 'Tommi', 'Rantanen', 'Helsinki', 'Finland'), (21, 'Erik', 'Kramer', 'Apeldoorn', 'Netherlands'), (22, 'Yuri', 'Shyrchenko', 'Poltava', 'Ukraine'), (23, 'Josef', 'Capek', 'Prague', 'Czechia'), (24, 'John', 'White', 'Birmingham', 'United Kingdom'); COMMIT;

3. Exporting multiple tables from SQLite database to SQL files in Exportizer Exnterprise:

"C:\Program Files (x86)\Vitalii Levchenko\Exportizer Exnterprise 9\exptizer.exe" /export /ExportType=SQL /ExportMode=REPLACE+INSERT /SuppressOverwriteOrDeletePrompt /TableMappingsFile=C:\MyData\SQLiteToSQL_TableMappings.xml /sqlSeparator=; /TargetDBType=POSTGRESQL /AddDDL /DDLAddCheckExistence /DropTable /sqlStatementType=BATCHINSERT /CommitInterval=1000 /encoding=UTF-8 /TrimTrailingSpaces /LineTerminator=WINDOWS /IncludeMemo /SrcDBInterface=fd /SrcDBKind=FILE /SrcDBDriver=SQLITE /SrcDB=C:\MyData\TestSQLiteDb.db /SrcSharedCache=True /SrcLockingMode=Exclusive "/TrgDB=C:\MyData\SQLite to SQL"

See also

Opening SQLite Databases

Command Line Usage

Practical Cases