topic Re: Adding an identity Column in Qlik Replicate

Adding an identity Column

nabeelaslam1994 — Wed, 01 Feb 2023 15:40:42 GMT

Hello Team,

Is there a way to add an identity column in qlik replicate? A target table will have new column and have 1,2,3,4 Row number generated by Qlik?

Re: Adding an identity Column

Gerald_U — Wed, 01 Feb 2023 15:42:26 GMT

Hello,

When the source table contains an identity column, Replicate does not create the identity column on the target table. In this case, the table will need to be created manually on the target endpoint.

https://help.qlik.com/en-US/replicate/November2022/Content/Replicate/Main/Introduction/Limitations.htm#Limitations%20and%20considerations

Regards,

Gerald

Re: Adding an identity Column

john_wang — Fri, 03 Feb 2023 12:24:07 GMT

Hello @nabeelaslam1994 ,

Yes, it's easy to do in Replicate - we can utilize the target side database itself auto-increase column properties. A sample :

1- In target side table, add an additional auto-increase column (in my sample it's "autoinc" in MySQL) and it's type is AUTO_INCREMENT

CREATE TABLE `scott`.`kitautoinc` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`autoinc` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`autoinc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

certainly the column type can be "identity(1,1)" in SQL Server, "generated by default as identity (start with 1 increment by 1)" in Oracle, "SERIAL" in PostgreSQL . Depends on the database type, you may refer to the corresponding database docs for exact syntax.

2- In Replicate task setting, set "If target table already exists" to "TRUNCATE before loading" (the default setting is "DROP and CREATE table")

3- In Replicate task table design, do not include the column, so far the target database will fill in the auto-increase column's values automatically, during both Full Load and CDC stage

4- In target side table the "autoinc" column values are the identify values

However please take note that the identity values maybe not continuous after some operations eg rows deletion etc, that's the database specific behavior.

Hope this helps.

Regards,

John.

Re: Adding an identity Column

nabeelaslam1994 — Fri, 03 Feb 2023 12:25:23 GMT

Hello

can we perform this auto-increment from Qlik replicate rather than creating a column in target database?

Nabeel

Re: Adding an identity Column

john_wang — Fri, 03 Feb 2023 12:33:33 GMT

Hello @nabeelaslam1994 ,

I'm afraid I did not get your question very well. if there are 2 columns in your source app (eg "id" and "name") then we need an additional column (eg "autoinc" in above sample) to store the identity column values.

If you are meaning the 'hidden' column eg 'rownum' in Oracle, then it's irrelevant to Replicate task at all. You may use it straightly in your query while consuming the target database data.

Regards,

John.

Re: Adding an identity Column

nabeelaslam1994 — Fri, 03 Feb 2023 12:38:16 GMT

Basically I am trying to see if there a setting in Qlik replicate that i can use to add auto increment feature rather than adding it on the target database(create script).

Re: Adding an identity Column

john_wang — Fri, 03 Feb 2023 12:44:33 GMT

Hello @nabeelaslam1994 ,

Well, maybe it's much easier for us if you can let me know your target database type, and if you wan to add such a column in all tables?

Regards,

John.

Re: Adding an identity Column

nabeelaslam1994 — Fri, 03 Feb 2023 13:06:33 GMT

It is snowflake. Yes to all the tables.

Re: Adding an identity Column

john_wang — Mon, 06 Feb 2023 11:28:39 GMT

Hello @nabeelaslam1994 ,

Please add an internal parameter in Snowflake target endpoint, the parameter name is

$info.query_syntax.create_table

and set its value to

CREATE ${TABLE_TYPE} TABLE ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} ( ${COLUMN_LIST} , autoinc integer NOT NULL AUTOINCREMENT (1,1) primary key)

where the additional column is "autoinc" in my above sample. please tune the SQL by following Snowflake create table syntax if you want to get more detailed control.

Regards,

John.

Re: Adding an identity Column

lacdsil — Fri, 19 May 2023 19:25:17 GMT

Thanks for posting it here. Works perfectly.

Re: Adding an identity Column

Sreekanth_333 — Fri, 01 Mar 2024 05:55:56 GMT

Hi @john_wang , Do we have similar internal parameter in target endpoint Microsoft SQL Server top create table with auto increment column ?

Re: Adding an identity Column

john_wang — Fri, 01 Mar 2024 07:51:22 GMT

Hello @Sreekanth_333 ,

IN general we can control the target side table creation behavior by internal parameter however there are some exceptions, unfortunately SQL Server is one of the exception.

Anyway, please allow us some time, we'd like to confirm for you if any chance. Will get back to you later.

Regards,

John.

Re: Adding an identity Column

DesmondWOO — Fri, 01 Mar 2024 10:41:51 GMT

Hi @nabeelaslam1994 , @Sreekanth_333 ,

John and I have tested. Changing syntax does not work on the SQL Server.

Regards,
Desmond