Import database from File PROGRAMMATICALLY?

Question

Is there a way to programatically import an entire database from a file with SQL?? (either .CSV , .SQL and .DB files are fine)

Thanks!!

EDITED AFTER TO CLARIFY:

I am interested in a solution that is Database independent (has to works with all types of databases (Mysql, SQL Server, PostGres, Oracle...)

Answer 1

MySQL: LOAD DATA INFILE for csv's; for .sql files generated with MySQL, use the shell.

---

For SQLite: see this SO question.

SQL Server: apparently there's the BULK INSERT command.

You are not going to find a database-independent syntax for an SQL command because there isn't one.

There may be a wrapper library around databases but I'm not aware of it. (Or you could try to use ODBC, but that's connection oriented and wouldn't allow direct access to a file)

Perhaps there is an interactive GUI-related software tool out there to do this.

Note also that loading data directly from a file on a database server into a database almost certainly requires security privileges to do so (otherwise it is a security risk).

Answer 2

Ok so I actually found a solution that is Database INDEPENDENT to import a database from a .Sql file quite easily!! :)

So whichever database you have (Mysql, Sqlite, ...) do the following:

1) export your database into .sql format. (This .sql file will contain all sql commands such as CREATE TABLE... INSERT INTO table...) (You may need to remove the lines that start with CREATE TABLE and leave only the lines that start with INSERT...)

2) Then in the language that you are using write some code that read each line of the Sql Lite files and stores it into an Array of String (String[])

3) Then execute each String contained in the array String[] as sql command

I've implemented this in Java:

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.util.LinkedList;
import java.util.List;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;

public class DatabaseImporter {

private static DatabaseImporter instance;

public static DatabaseImporter getInstance(){
    if(DatabaseImporter.instance == null)
        instance = new DatabaseImporter();

    return DatabaseImporter.instance;
}

private DatabaseImporter (){

}

public void importDatabaseFromFile(Context context, String databaseName , String filePath){

    SQLiteDatabase database = //CREATE UR DATABASE WITH THE COMMAND FROM THE DATABASE API YOUR USING

    this.executeSqlCommands(    database                                , 
                                this.readSqlCommandsFromFile(filePath)
                            );
}


private String[] readSqlCommandsFromFile(String filePath){

    String[] sqlCommands = new String[0];
    List<String> sqlCommandsList = new LinkedList<String>();

    try{

        // Open the file that is the first 
        // command line parameter
        FileInputStream fstream = new FileInputStream(filePath);
        BufferedReader br = new BufferedReader(new InputStreamReader(fstream));
        String strLine;
        //Read File Line By Line
        while ((strLine = br.readLine()) != null)   {
            if(!strLine.equals("") && !strLine.equals(" ") && strLine != null)
                sqlCommandsList.add(strLine);
        }
        //Close the input stream
        in.close();

    }catch (Exception e){//Catch exception if any
        System.err.println("Error: " + e.getMessage());
    }

    sqlCommands = new String[sqlCommandsList.size()];

    sqlCommandsList.toArray(sqlCommands);

    return sqlCommands;
}


private void executeSqlCommands(SQLiteDatabase database, String[] sqlCommands){

    for(int i = 0; i < sqlCommands.length ; i++){


        database.execSQL(sqlCommands[i]);
    }
}

}

Answer 3

mysql -u -p < dumpfile.sql

importing csv would require a script (using eg. PHP) to put the right fields in the right bit of the query.

Answer 4

If you are using SQL Server check out SSIS