Hi Team

[NNekkanti1658462243]

Hi team ,i have sql script which needs to be run in talend and schedule in tac.The script has multiple select statements and joins from different table.Which component is usefull.I have used tdbrow but it did'nt works .Pls help me out how to execute and schedule

[Anonymous]

Hi @Navyasri Nekkanti​ , DBInput is used to execute a select statement, copy your sql script and paste it to the Query field of tDBInput, try again.

Regards

Shong

[NNekkanti1658462243]

Hi shong, I have taken tdbinput ,there I have taken select statement and I connected to tdbrow and I have given insert statement in tdbrow,but data is not inserting . Pls help me out Regards N Navyasri Engineer

[Anonymous]

I'm afraid we need more details about your issue, do you have any errors on the job execution? or the job runs without error, but no data is inserting?

[NNekkanti1658462243]

Hi Shong,

pls find below which need to be inserted

insert into [Transfer plan for production]

SELECT 'ALU' AS MATERIAL,sum([number of alu reels existing in wh]) AS [STOCK ON PLANT]--SUM([Number of Reels exisitng in WH]) AS [STOCK1]

,sum([ALU to be transfered]) AS [FOR TRANSFER]

,CEILING(SUM([Vehicle Fill Rate for ALU])) AS [NO OF TRIP],getdate()

FROM (

select sum([number of alu reels existing in wh]) as [number of alu reels existing in wh],ALMAT -- as [Number of Reels to be Transfered]

from MARD_logic_number_of_reels a

where created_date=format(DATEADD(day,0,GETDATE()),'yyyy-MM-dd')

group by ALMAT

)TEMP1

LEFT JOIN 

(

select sum([ALU to be transfered]) as [ALU to be transfered],[ALumaterial_number] 

,SUM([Vehicle Fill Rate for ALU]) AS [Vehicle Fill Rate for ALU]

from alMAT_datamart

where created_date=format(DATEADD(day,0,GETDATE()),'yyyy-MM-dd')

group by [ALumaterial_number]

)TEMP2

ON TEMP1.ALMAT=TEMP2.[ALumaterial_number]

UNION 

SELECT 'LPB' AS MATERIAL,sum([number of lpb reels existing in wh]) AS [STOCK ON PLANT],--SUM([Number of reels existing in wh]) AS [STOCK1],

sum([Number of Reels to be Transfered]) as [For Transfer]

--sum) AS [FOR TRANSFER]

,max(s.container_number) AS [NO OF TRIP],getdate()

--,CEILING(SUM([QUANTITY])) AS [NO OF TRIP]

from 

(

select sum([number of lpb reels existing in wh]) as [number of lpb reels existing in wh],rkmat -- as [Number of Reels to be Transfered]

from MARD_logic_number_of_reels a

where created_date=format(DATEADD(day,0,GETDATE()),'yyyy-MM-dd')

group by rkmat

)temp1

--rkmat_datamart z

LEFT join (

select max(container_number) as container_number,material_number from [dbo].[RK_MART_trip]

where created_date=format(DATEADD(day,0,GETDATE()),'yyyy-MM-dd')

group by material_number) s

on s.material_number=temp1.rkmat

left join

(select sum([Number of Reels to be Transfered]) as [Number of Reels to be Transfered] ,[LPB Material Number] 

from rkmat_datamart z

where created_date=format(DATEADD(day,0,GETDATE()),'yyyy-MM-dd')

group by [LPB Material Number]

)

aa

on temp1.rkmat=aa.[LPB Material Number]

Here we are combing multiple table to insert data.

I have use tmssqlinput alone.But it is not working.I have use tdbrow component but data is not inserting.I did'nt find any error also.

Pls help me how to insert data into db with the above query.

[jlolling]

I do not understand what is your problem. You can run your statement simply in a tDBRow component.

The tDBInput component cannot do that because it expects always a select statement which provides a result set.

A tDBRow component can run any statements.

If you have multiple statements you could use the custom component tSQLScriptParser (get it from Talend Exchange).

This component parse a script and triggers a tDBRow component to run each statement.