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)) ;

score 14 · Answer 1 · answered Mar 02 '17 at 12:40

14

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" ) );

answered Mar 02 '17 at 12:40

Manish

3,443
1
21
24

4

I'd suggest using DB::statement() – feskr Mar 04 '19 at 10:24
@feskr You were right its better to use DB::statement("create temporary table x") – Abilogos Feb 04 '20 at 11:28

score 1 · Answer 2 · answered Jul 27 '21 at 13:37

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);

Please explain how is this temp table ? – Vipertecpro Jun 03 '23 at 05:20 — Vipertecpro, Jun 03 '23 at 05:20

score 1 · Answer 3 · answered Feb 15 '22 at 11:15

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();

score -6 · Accepted Answer · answered Mar 06 '17 at 15:02

-6

With Schema you can create temp tables...

 Schema::create('temp', function (Blueprint $table) {
                $table->increments('id');
                $table->string('session_id');

            });

answered Mar 06 '17 at 15:02

Abbas Ahmad

377
3
4
13

7

The method [temporary()](https://laravel.com/api/5.4/Illuminate/Database/Schema/Blueprint.html#method_temporary) could be used: `$table->temporary();`... – Sᴀᴍ Onᴇᴌᴀ May 16 '17 at 23:41
5

this answer does not create a temporary table. – Tim Ogilvy Jul 25 '18 at 02:37

How to create TEMPORARY table in laravel

4 Answers4

Linked