How to create a temp table in mysql

ServiceStack.OrmLite
1

Trying to create a temp table with ormlite.

Would I use a filter to catch the ‘create table’ and convert it to a ‘create TEMPORARY table’ ???

https://dev.mysql.com/doc/refman/5.6/en/create-table.html

2

You can get the CREATE TABLE SQL with:

var createSql = db.GetDialectProvider().ToCreateTableStatement(typeof(Table));
3

That gives me the SQL for the create table of the class I provide as Table. Then I replace create table’ with ‘create TEMPORARY table’ and execute it?

4

Right OrmLite doesn’t explicitly support temporary tables but you should be able to modify the SQL to add the TEMPORARY keyword with something like:

var createSql = db.GetDialectProvider().ToCreateTableStatement(typeof(Table));
var parts = createSql.SplitOnFirst(' ');
var tempSql = parts[0] + " TEMPORARY " + parts[1];
db.ExecuteSql(tempSql);
5

Thanks!!

public static class OrmLiteMySqlExtensionMethods
{
    public static int  CreateTempTable<T>(this IDbConnection connection)
    {
        var createSql = connection.GetDialectProvider().ToCreateTableStatement(typeof(T));
        var ncreate = createSql.Replace("CREATE TABLE", "CREATE TEMPORARY TABLE");
        return connection.ExecuteSql(ncreate);
    }
}
1 Like