Seeding Microsoft SQLServer and Identity_Insert

1

I need to run some seeds where the identity is part of the seed…

This is easy enough in mysql but I need to also support MS SqlServer (especially given there is “free for ever” SQL options in Azure).

Anyways, I need to find a way to get the session that is used for the bulk insert includes the SET IDENTITY_INSERT command.

I have the following code I am working from

   $table = $this->table('branches');
    if ($table->getAdapter()->getConnection()->getDriver() instanceof \Cake\Database\Driver\Sqlserver) {
        $this->execute('SET IDENTITY_INSERT dbo.branches ON;');
    }
    $table->insert($data)->saveData();
    if ($table->getAdapter()->getConnection()->getDriver() instanceof \Cake\Database\Driver\Sqlserver) {
        $this->execute('SET IDENTITY_INSERT dbo.branches OFF;');
    }

However for the life of me I can’t get the table to use the same connection/session as the seed… I have tried setting the adapter explicity ($table->setAdapter($this->getAdapter())), I have tried starting a transaction, I have tried explicitly connecting the adapter ($this->getAdapter()->connect())…

Are there any good ideas out there how to make sure the same connection/session context is being used? or is there a way to like have a generic action that I can queue on the table where I can just hamfistedly force in my SET command before the bulkInsert?

Thanks in advance

2

As an update I hacked in the Set Identity call in the same SQL that the BulkInsert uses (just to confirm if it all goes togeather it would be fine) and it did work… obviously changing PDOAdapter.php is not a ‘wise’ decision so while it validated sql will play nice with identity_insert being turned off, its not a usable approach.

Is there a way I can use my own Adapter? or should I be looking at inheriting the SQLDriver and making a custom SQLDriver that has a new property to know when to mess with query and execute to prepend and append the identity_insert call into the SQL?=

3

Any official guidence or suggestions on this issue?

I would love to get my app working with MS SQL and this is a bit of a blocker..