Pentaho Data Integration – Update Identity Column in Microsoft SQL Server

identitypentahosql server

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?

Best Answer

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
Related Question