Workaround for Identity column in APS

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