Resolve an issue to insert explicit value for identity column in SQL table asking for IDENTITY_INSERT to set ON/OFF in PHP (Laravel)

The following methods to control the `IDENTITY_INSERT` to ON/OFF for SQL table seeding process in PHP (Laravel) :

    
    function setIdentityInsert($table, $onStatus = true)
    {
        $status = $onStatus ? 'ON' : 'OFF';

        $this->sqlConnection->unprepared("SET IDENTITY_INSERT $table $status");
    }

    function insertTableData($table, $data)
    {
        return $this->sqlConnection->table($table)->insert($data);
    }

    function seedTable($table, $hasAutoIncrementPrimaryKey = false, $data = [])
    {
        if ($hasAutoIncrementPrimaryKey) {
            $this->setIdentityInsert($table);
            $response = $this->insertTableData($table, $data);
            $this->setIdentityInsert($table, false);

            return $response;
        }
        else {
            return $this->insertTableData($table, $data);
        }
    }

Note: Generally, the table requires to have an auto-increment primary key to set Identity Insert to `ON`, that's why I have the `$hasAutoIncrementPrimaryKey` flag. Otherwise, seeding may throw an error as:

    
    SQLSTATE[HY000]: General error: 544 Cannot insert explicit value for
    identity column in table 'test_table_name' when IDENTITY_INSERT is set to
    OFF. [544] (severity 16) [(null)]

Hope this helps!

Get a list of columns and their data type by passing table name on MySQL and MSSQL databases.

To get a list of columns and their data type by passing table name on MySQL and MSSQL databases.

    
    /**
     * Returns the list of columns and their data type by the table name on MySQL database.
     * @param $sqlConnection MySQL Database Connection Link
     * @param $tableName
     * @param bool $fullType
     * @return array
     */
    private function getMySqlTableColumnAndTypeList($mysqlConnection, $tableName, $fullType = false)
    {
        $table = $mysqlConnection->select("DESCRIBE $tableName");

        $fieldAndTypeList = [];
        foreach ($table as $field){
            $type = ($fullType || !str_contains($field->Type, '('))? $field->Type: substr($field->Type, 0, strpos($field->Type, '('));
            $fieldAndTypeList[$field->Field] = $type;
        }
        return $fieldAndTypeList;
    }

    /**
     * Returns the list of columns and their data type and nullable by the table name on MSSQL database.
     * @param $sqlConnection SQL Database Connection Link
     * @param $tableName
     * @return array
     */
    private function getSqlTableColumnsWithDataTypes($sqlConnection, $tableName)
    {
        $tableInfo = $sqlConnection->select("SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '". $tableName . "'");

        $fieldAndTypeList = [];
        foreach ($tableInfo as $field){
            $isNullable = ($field->IS_NULLABLE == 'YES') ? true : false;
            $fieldAndTypeList[$field->COLUMN_NAME] = (object)['type' => $field->DATA_TYPE, 'nullable' => $field->IS_NULLABLE, 'isNullable' => $isNullable];
        }

        return $fieldAndTypeList;
    }

Raw Query in MSSQL:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name';

Raw Query in MySQL:

DESCRIBE your_table_name;

Appreciated for any feedback!