Pentaho Data Integration - Kettle- Update Identity Column in Microsoft SQL Server

Question

For migration purposes I need to migrate existing ID's from my old database to my new table in MS SQL Server.

I can truncate my table in MS SQL Server and then adjust my database connection in Pentaho to 'SET IDENTITY_INSERT tablename ON'. Then I am able to insert my ID's through a table output step, but I want to be able to UPDATE my existing ID without truncating my table in MS SQL Server.

Could this be possible using Pentaho?

Answer 1

SQL Server does not allow updates the IDENTITY column value regardless of application technology.

One way to perform such a migration is to switch data into a table with identical schema (except for the IDENTITY property), perform the update, and then SWITCH back into the main table.

CREATE TABLE dbo.TableWithIdentity(
      IdentityColumn int IDENTITY NOT NULL
        CONSTRAINT PK_TableWithIdentity PRIMARY KEY
    , OtherData varchar(20) NULL
    );
CREATE INDEX idx_TableWithIdentity_OtherData
    ON dbo.TableWithIdentity(OtherData);
INSERT INTO dbo.TableWithIdentity VALUES
      ('Sample data one')
    , ('Sample data two')
    , ('Sample data three');
GO

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    --create staging table with same schema, indexes, and constraints but without IDENTITY
    CREATE TABLE dbo.TableWithoutIdentity(
          IdentityColumn int NOT NULL
            CONSTRAINT PK_TableWithoutIdentity PRIMARY KEY
        , OtherData varchar(20) NULL
        );
    CREATE INDEX idx_TableWithoutIdentity_OtherData
        ON dbo.TableWithoutIdentity(OtherData);

    --SWITCH data into staging table 
    ALTER TABLE dbo.TableWithIdentity
        SWITCH TO dbo.TableWithoutIdentity;

    --example update to change identity value
    UPDATE TableWithoutIdentity
    SET IdentityColumn = IdentityColumn + 100;

    --SWITCH data back into main table 
    ALTER TABLE dbo.TableWithoutIdentity
        SWITCH TO dbo.TableWithIdentity;

    DBCC CHECKIDENT(N'dbo.TableWithIdentity');

    DROP TABLE dbo.TableWithoutIdentity;

    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END CATCH;
GO