Workaround for Identity column in APS

  • Article

While working on a problem which involves migrating SSIS package from SQL Server to APS, there was extensive use of Identity column in data warehouse, and as there is no support for Identity column in APS till now, I have to come up with some workaround.

Adding to this problem there is one more limitation, that while dumping data to APS using SQL Server PDW Destination Adapter you have to provide a mapping for destination column (which in normal scenario one will left it out for Identity columns), which means that for identity column you have to provide a dummy column with a dummy value.

So, I have written a stored procedure, which will update the Identity column value from dummy value to the next biggest value already present in the table + 1 (and if table does not have any entry, then it will take 1 as the starting point)

This SP expects 4 parameters

  1. TableName: Table Name
  2. Shema: Schema of the table
  3. IdentityColName: Identity column name
  4. UpdatableValue: Dummy value which needs to be updated

CREATE PROC [dbo].[uspUpdateIdentityColumnValue] @TableName [varchar](100),@Schema [varchar](100),@IdentityColName [varchar](100),@UpdatableValue [int] AS

BEGIN

/***************************************************************************** 

PROCEDURE NAME:      [uspUpdateIdentityColumnValue]

AUTHOR:              Siddharth

CREATED:             09/22/2015

 

Example:

DECLARE

       @IdentityColName varchar(100) = 'TableID'

       ,@TableName varchar(100) = ‘TableName'

       ,@Schema varchar(100) = 'dbo'

       ,@UpdatableValue int = -1

EXEC [dbo].[uspUpdateIdentityColumnValue] @TableName,@Schema,@IdentityColName,@UpdatableValue

 

VERSION HISTORY: 

Date                 Change Description 

Sep 22, 2015         Created

****************************************************************************/

      

       BEGIN TRY

              DECLARE @ErrorMessage NVARCHAR(4000);

              DECLARE @ErrorSeverity INT;

              DECLARE @ErrorState INT;

 

              DECLARE @TempTableName varchar(100)

                     ,@TableFullName varchar(100)

                     ,@TempTableFullName varchar(100)

                     ,@Sql nvarchar(4000)

                     ,@IdentityColDataType varchar(50)

                     ,@ColNames varchar(1000) = ''

                     ,@Index int = 1

                     ,@Count int

             

              ---------Create table names

              SET @TempTableName = @TableName + '_Tmp_001'

              SET @TempTableFullName = @Schema + '.'+@TempTableName

              SET @TableFullName = @Schema + '.'+@TableName

 

              IF object_id('tempdb.dbo.#ColNames') IS NOT NULL

                     DROP TABLE dbo.#ColNames

 

              ----------------Get All column names

              CREATE TABLE dbo.#ColNames WITH(LOCATION = USER_DB, CLUSTERED COLUMNSTORE INDEX,DISTRIBUTION = REPLICATE) AS

              SELECT ROW_NUMBER() OVER(ORDER BY COLUMN_NAME) ID, COLUMN_NAME

              FROM INFORMATION_SCHEMA.COLUMNS

              WHERE Table_NAME = @TableName

              AND TABLE_SCHEMA = @Schema

              AND COLUMN_NAME != @IdentityColName

 

              SET @IdentityColDataType = (SELECT DATA_Type

              FROM INFORMATION_SCHEMA.COLUMNS

              WHERE Table_NAME = @TableName

              AND TABLE_SCHEMA = @Schema

              AND COLUMN_NAME = @IdentityColName)

 

              -----------------concatenate the list of columns

              SET @Count = (SELECT COUNT(*) FROM #ColNames)

              WHILE(@Index<=@Count)

              BEGIN

                     SET @ColNames = @ColNames + (SELECT COLUMN_NAME FROM #ColNames WHERE Id = @Index) + ','

                     SET @Index+=1

              END

              SET @ColNames = (SELECT SUBSTRING(@ColNames,1,LEN(@ColNames)-1))

 

 

              IF OBJECT_ID(@TempTableFullName) IS NOT NULL

              BEGIN

                     SET @Sql = N'DROP TABLE ' + @TempTableFullName

                     EXEC sp_executesql @Sql

              END

 

 

              SET @Sql = '

                                  DECLARE @MaxID int

 

                                  SET @MaxID = (SELECT ISNULL(MAX(' + @IdentityColName + '),0) FROM ' + @TableFullName +' WHERE ' + @IdentityColName + '! = ' + CAST(@UpdatableValue AS varchar(5))+ ')

                    

                                  CREATE TABLE ' + @TempTableFullName + '

                                  WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = REPLICATE)

                                  AS

                                 

                                  SELECT ' + @IdentityColName + ',' + @ColNames+'

                                  FROM ' + @TableFullName + '

                                  WHERE ' + @IdentityColName + '! = ' + CAST(@UpdatableValue AS varchar(5)) + '

                                  UNION

                                  SELECT ISNULL(CAST(ROW_NUMBER() OVER(ORDER BY ' + @IdentityColName + ') + @MaxID AS ' + @IdentityColDataType + '),-1) AS ''' +@IdentityColName + ''',' + @ColNames+'

                                  FROM ' + @TableFullName + '

                                  WHERE ' + @IdentityColName + ' = ' + CAST(@UpdatableValue AS varchar(5)) + '

                           '

              --SELECT @SQl

              IF LEN(@Sql) > 3999

              BEGIN

                     SET @ErrorMessage = 'String exceeds limit'

                     SET @ErrorSeverity = 16

                     SET @ErrorState = -1

 

                     RAISERROR (@ErrorMessage,

                                  @ErrorSeverity,

                                  @ErrorState

                                  );    

              END

              ELSE

              BEGIN

                     EXEC sp_executeSQL @Sql

                     SET @Sql = N'

                           IF OBJECT_ID(''' + @TableFullName + ''') IS NOT NULL

                                  DROP TABLE ' + @TableFullName + '

 

                           IF OBJECT_ID(''' + @TempTableFullName + ''') IS NOT NULL

                                  RENAME OBJECT ' + @TempTableFullName + ' TO ' + @TableName + '

                     '

                     EXEC sp_executesql @Sql

              END

              IF object_id('tempdb.dbo.#ColNames') IS NOT NULL

                     DROP TABLE dbo.#ColNames

             

 

       END TRY

       BEGIN CATCH

                 

              SET @ErrorMessage = ERROR_MESSAGE()

              SET @ErrorSeverity = ERROR_SEVERITY()

              SET @ErrorState = ERROR_STATE()

 

              RAISERROR (@ErrorMessage,

                         @ErrorSeverity,

                         @ErrorState

                         );

             

              IF(@@TRANCOUNT > 0)

                     ROLLBACK;

       END CATCH    

END

 

 

Limitations 

  1. In the script, I am using dynamic SQL, and there is a limitation on the maximum size of nvarchar i.e. 4000. So the script might break in case the script exceeds the limit