Migration Command to import table daa in Laravel 7

Question

By using migration I am able to import the database structure of a Laravel website. But what is the best and correct way to import its data as well.

Is there any command available in migration?

Need help. I am using Laravel 7.

Thanks

Answer 1

This problem is common when you have to migrate a table with its data to other project like Laravel.

Proposed Solution

1. Dump the data of table you want to migrate to a .sql file and place that file some where in the folder you can access it. For ease let place the file inside public folder.

2. Make a command to import that .sql file.

3. Make a seeder and call that command in run function.

Implementation Guidance

Make a command

php artisan make:command ImportXTable

This command will create a new command class in the app/Console/Commands directory.

After generating your command,goto app/Console/Commands directory and open the file you have just created i.e ImportXTable, you should fill in the signature and description properties of the class, which will be used when displaying your command on the list screen. The handle method will be called when your command is executed. You may place your command logic in this method.

Sample Script

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class ImportXTable extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'import:xtable';//this is what you will call  to import table

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Import the .sql file for ImportXTable';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $sql = public_path('fileNameToImport.sql');// write the sql filename here to import
        
        
        DB::unprepared(file_get_contents($sql));
    }
}

Then in kernel.php in the commands array register your command as follows

  protected $commands = [
        Commands\ImportXTable::class,
    ];

Seeder Implementation

Make a Seeder for Xtable and Call that command in seeder as follows

<?php

use Illuminate\Database\Seeder;

class XTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        \Artisan::call('import:xtable');//calling the command to import data from .sql file to database table
    }
}

Now when ever you run your seeder the file will be imported from dump sql to your table.

Remember to configure your sql file to get imported to your desired table and keep the name of table same, or you can edit .sql file in editor

For Example You can Edit your Sql file and wrote this

insert  into `x_table`(`column_name1`,`column_name2`,`column_name3`) values 

(0,16777215,'-','-'),
............