How to create TEMPORARY table in laravel

Question

how to create a TEMPORARY table in laravel, insert a record and retrieve hello, I'm trying to create a temp table in laravel and insert a record and retrieve that record from temp table and then drop the table.

But my temp table is not created

DB::raw(“CREATE TEMPORARY TABLE tbl_temp(temp_id VARCHAR(100),tempcolumn1 VARCHAR(100),tempcolumn2 VARCHAR(100),tempcolumn3 VARCHAR(100)) ;

Answer 1

Try this

// CREATE TEMPORARY TABLE

$productList = DB::insert( DB::raw( "CREATE TEMPORARY TABLE tempproducts") );

// DELETE TEMPORARY TABLE

$dropTable = DB::unprepared( DB::raw( "DROP TEMPORARY TABLE tempproducts" ) );

Answer 2

I have recently created a temp table( laravel 8) as such:

public function createLocalStoreProductTable(): \Illuminate\Http\JsonResponse
    {
        $tempTable = env("DB_TEMP_STORE_PRODUCT_TABLE_NAME");
        DB::connection('mysql')->statement(
            "CREATE TABLE " . $tempTable . " (
                    `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
                    `store_uid` int(10) unsigned NOT NULL)
                     PRIMARY KEY (`uid`),
                     KEY `store_uid` (`store_uid`)
                     ) ENGINE=InnoDB AUTO_INCREMENT=3035849 DEFAULT 
                       CHARSET=utf8;"

 return response()->json(array('success' => true), Response::HTTP_OK);

Answer 3

If you want a temporary table containing the result of a query:

DB::statement('CREATE TEMPORARY TABLE your_table_name SELECT ...insert query here');

If you want to define the table before inserting rows:

DB::statement('CREATE TEMPORARY TABLE your_table_name (...insert normal DDL here)');

Then just insert rows as in a normal table:

DB::table('your_table_name')->insert(["column" => "value"]);

or get result as in normal table:

DB::table('your_table_name')->get();