Dynamic schema : no primary key ?

Six Stars

Dynamic schema : no primary key ?

Hello !


I would like to transfer the content of a table into another database, while keeping the exact same structure.

I used the MSSQL Input & output components, with a dynamic schema, to allow easy modifications of the source table.

However, if I do that, I can't have a primary key, the new table is created without one, which seems quite weird. Is there any way to change that ? Or should I use a more rigid schema with a primary key ? Thanks if you have any comment ! 

Community Manager

Re: Dynamic schema : no primary key ?

Instead of outputting your data to a single Dynamic column, output your primary key column AND a Dynamic schema column. I am assuming that your PK will be called something consistently across your tables ("id" for example), but this might help. The Dynamic schema is able to work like this.

Six Stars

Re: Dynamic schema : no primary key ?

Oh ok, I didn't know that. So I can mix some fixed columns (the one(s) used for the primary key), and the dynamic column type for everything else, if I understand this right. Very interesting, thanks for this feedback ! I will try this solution. 

Community Manager

Re: Dynamic schema : no primary key ?

I've not tested this exhaustively, but I do know you can separate known columns out.

Six Stars

Re: Dynamic schema : no primary key ?

I tried, but I'm getting errors :


I have change my schema to 



Field 2

Dynamic field

(Field 1 & 2 being the primary key)


Then my SQL query is "SELECT Field1, Field2, * FROM table"

But then it gives me field format errors, saying that it can convert a value to an integer. 


I guess I should spent a bit more time to list the useful columns and add the new ones later manually. Too bad I can't easily import the full table with the primary key using Talend ! 

Community Manager

Re: Dynamic schema : no primary key ?

Lets say your table looks like this.....


CREATE TABLE [dbo].[Batch](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[catalogue_id] [bigint] NULL,
	[datasource] [varchar](max) NULL,
	[start_time] [datetime] NOT NULL,
	[end_time] [datetime] NULL,
	[status_id] [bigint] NULL,

	[id] ASC

In this case your schema would need to be ...

id = Long

row = Dynamic 


Your query could be...


Select *
From Batch

This has worked for me.

Six Stars

Re: Dynamic schema : no primary key ?

I think it works because your primary key is the first one in the table. 

I tried what you proposed, to simply use a select *, with the schema you gave. But it's not working, as it's expecting the primary key fields to be the first in the query. Unfortunately, in  my case, they are not, and it's often like that. So I think it's unfortunately more complicated for me. Unless I'm missing an option ?

Community Manager

Re: Dynamic schema : no primary key ?

Have you used your primary field names or just used random names? But you are correct, my pks are always at the start of the table.
Six Stars

Re: Dynamic schema : no primary key ?

In my tests, I used the primary key field names yes, and I immediately got error. I thought that maybe Talend would not mind receiving all the columns in a different order. But apparently it does, so unless I have my primary key field(s) at the beginning of my tables, the dynamic solution seems to be impossible unfortunately. Not a huge deal, and at least I know better how it works now ! Thanks for the time spent thinking about it.


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables


How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration


APIs for Dummies

View this on-demand webinar about APIs....

Watch Now