topic Copy selected columns from source table to destination table in Talend Studio

Copy selected columns from source table to destination table

Anonymous — Wed, 01 May 2013 15:18:28 GMT

Hi,
I am playing around with TOS DI and finding my feet a little, it seems like a really good product.
What I am trying to do is copy all the columns apart from the identity column from one table to a renamed copy of the exact table in MS SQL Server.
In TOS my job I have a tMSSqlInput that is correctly configured to the DB and SQL query is good. This is connected to a tMap which in turn is connected to a tMSSqlOutput which is pointing at the destination table. What I am finding confusing is when I double click my tMap object to go into Map Editor there are no columns on either side, I was expecting as I had declared a source and destination table I would see these columns in there.
Obviously I have got my wires crossed. Can anyone point out where I am going wrong please.
Thanks

Re: Copy selected columns from source table to destination table

janhess — Wed, 01 May 2013 15:32:33 GMT

Have you joined up the components?

Re: Copy selected columns from source table to destination table

Anonymous — Wed, 01 May 2013 15:38:34 GMT

Yes they are joined, I should have said so though.

Re: Copy selected columns from source table to destination table

janhess — Wed, 01 May 2013 15:39:57 GMT

What version? When you join the dbinput to the map it should take it's schema, same for the output.
Can you post an image of the job.

Re: Copy selected columns from source table to destination table

Anonymous — Wed, 01 May 2013 16:02:00 GMT

Trying again

Re: Copy selected columns from source table to destination table

janhess — Wed, 01 May 2013 16:28:45 GMT

Looks ok. Have you checked that the input and output actually have schemas?

Re: Copy selected columns from source table to destination table

Anonymous — Thu, 02 May 2013 07:08:26 GMT

Hi,
When you set up your DB connection, you should define the related schema and then use a "Main" row to link tMssqlinput and tMap.
Please see the related reference: Setting up a DB connection.
In addition, I have seen there is a error icon in your tMap(Maybe it is caused by missing schema)we usually open the code tab and see if there has compile error, then, close the job and open it again, the x-mark appears. If the job has compile error, you are always required to fix the error before executing the job.
Best regards
Sabrina

Re: Copy selected columns from source table to destination table

Anonymous — Thu, 02 May 2013 08:55:16 GMT

I just deleted all of the objects and re added them and the mappings work now. Not a clue what the issue was but it will be "me" related I think.

Re: Copy selected columns from source table to destination table

Anonymous — Thu, 02 May 2013 09:09:35 GMT

Hi,
Thanks for your feedback. Perhaps sometimes components are failure, you should try a few more. Delete it in designer and drag a new one into it or restart your studio to see if this issue repro again.
Best regards
Sabrina

Re: Copy selected columns from source table to destination table

Anonymous — Thu, 02 May 2013 09:16:53 GMT

Hi Sabrina,
Many thank you to you and the others for helping me with this.
Can I ask is it possible to change the default insert in this job?
Looking at the java code at the moment it is something like:
INSERT INTO tblCampainTest
(
,
,
,
,
,
,
,

)
VALUES
(
?,
?,
?,
?,
?,
?,
?,
?
)
However this column id is an identity so it should just be:
INSERT INTO tblCampainTest
(
,
,
,
,
,
,

)
VALUES
(
?,
?,
?,
?,
?,
?,
?
)
I would think I could just go in and edit the generated java code but is there a more preferred way?
If I need to start a new post for this it is not a problem.
Thanks again, Dave.

Re: Copy selected columns from source table to destination table

Anonymous — Thu, 02 May 2013 09:47:16 GMT

Sorry I now realise I can't "just go in and edit the generated java code"

Re: Copy selected columns from source table to destination table

Anonymous — Thu, 02 May 2013 09:47:22 GMT

Hi,
I think your issue refer to this forum https://community.talend.com/t5/Design-and-Development/add-column-to-an-Oracle-table/td-p/105797
Best regards
Sabrina

Re: Copy selected columns from source table to destination table

Anonymous — Thu, 02 May 2013 10:30:31 GMT

I edited the schema of the output table and removed the id field. It works great now. Thanks again Sabrina. I like this IDE but it is just a case of getting used to it.

Re: Copy selected columns from source table to destination table

Anonymous — Thu, 02 May 2013 10:58:53 GMT

Hi Dave,
Talend offers a rich set of online and instructor-led training courses to speed your ramp-up time, deliver projects faster, and maximize your Talend investment.
Best regards
Sabrina