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 !
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.
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.
I tried, but I'm getting errors :
I have change my schema to
(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 !
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.
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 ?
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.
Kickstart your first data integration and ETL projects.
Part 2 of a series on Context Variables
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema