Dynamic schema : no primary key ?

[François_T]

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 ! 

[Anonymous]

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.

[François_T]

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. 

[Anonymous]

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

[François_T]

I tried, but I'm getting errors :

I have change my schema to 

Field1

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 ! 

[Anonymous]

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,

 CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED 
(
	[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.

[François_T]

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 ?

[Anonymous]

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.

[François_T]

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.