MySQL Stored Procedures

I see, that is interesting.

What I am also doing is taking a database I have stored as a different name, and making it make more sense by including indexes, foreign keys, and more pivot tables to reduce the amount of columns on some of my tables in a separate database. When I migrate:refresh, is there a way after pull data from my old database into the one I am working on that is widely used? If not would an unprepared INSERT INTO SELECT work best?

I think I would just dump the old database and place it in the database/schema folder as a starting point (see: https://laravel.com/docs/8.x/migrations#squashing-migrations).

Then I would generate the migrations to create the additional pivot tables, indexes, foreign keys, etc.

I don't think you can make INSERT INTO SELECT work with two different databases using the DB facade (since you need to specify a connection, which it's tied to a specific database).

PS: You could manually import them using by chunking the results:

DB::connection('db1')->table('users')->orderBy('id')->chunk(100, function ($users) {

    foreach ($users as $user) {
        DB::connection('db2')->table('users')->insert([/* ... */]);
    }
});

Perhaps I can move the old into the new as jobs_old do an INSERT INTO SELECT into jobs then remove jobs_old.

CREATE DEFINER=root@% PROCEDURE jobs_old() BEGIN CREATE TABLE laravel.jobs_old SELECT * FROM old.data_jobs; END

Worked like a charm, thank you so much.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class Jobs extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });

        Schema::create('password_resets', function (Blueprint $table) {
            $table->string('email')->index();
            $table->string('token');
            $table->timestamp('created_at')->nullable();
        });

        Schema::create('failed_jobs', function (Blueprint $table) {
            $table->id();
            $table->string('uuid')->unique();
            $table->text('connection');
            $table->text('queue');
            $table->longText('payload');
            $table->longText('exception');
            $table->timestamp('failed_at')->useCurrent();
        });
        //VOS
        Schema::create('jobs', function (Blueprint $table) {
            $table->id();
            $table->text('customer');
            $table->text('project');
            $table->text('contact');
            $table->text('billing');
            $table->text('reference');
            $table->text('project_manager');
            $table->text('salesperson');
            $table->timestamps();
        });
        //Import Old
        DB::unprepared('
        CREATE PROCEDURE jobs_old()
            BEGIN
            CREATE TABLE laravel.jobs_old SELECT * FROM old.data_jobs;
            INSERT INTO laravel.jobs (`id`, `customer`, `project`, `contact`, `billing`, `reference`,`project_manager`,`salesperson`,`created_at`,`updated_at`)
            SELECT `jobnumber`, `customer`, `project`, `contact`, `billing`, `reference`,`pm`,`salesperson`,`modified`,`modified`
            FROM laravel.jobs_old;
            END
        ');
        DB::unprepared('CALL jobs_old()');
    }
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users');
        Schema::dropIfExists('password_resets');
        Schema::dropIfExists('failed_jobs');
        Schema::dropIfExists('jobs');
        Schema::dropIfExists('jobs_old');
        DB::unprepared('DROP PROCEDURE IF EXISTS jobs_old');
    }
}

Great, happy to hear it!

I read through that link and others prior to posting this. This deals with a select, Stored Procedures can deal with update, insert, delete and multiple lines of those. Is it okay to use Select for all of those or is what @guybrush_threepwood mentioned better?

guybrush_threepwood content is better.