Deleting rows before inserting into SQL Server 2016

Highlighted
Five Stars

Deleting rows before inserting into SQL Server 2016

Using this query, I find the date for which I want all rows with a greater date deleted. The table is sorted by this date.

SELECT TOP 1 ThisDate, ID
FROM [Database].[dbo].[Table]
WHERE ThisDate < CONVERT(DATE, GETDATE()-30)

ORDER BY ThisDate DESC, ID DESC

This is what I want to do with ThisDate:

DELETE FROM  [Database].[dbo].[Table

WHERE ThatDate > ThisDate

 

Despite much searching, I can find no clear explanation on how to do this with tMSSqlOutput or any other component. I can use tSetGlobalVar to pass a value into a query, but which component can execute a delete statement? I really don't care how this can be done, but I want to do it within Talend MDM DI instead of calling a stored procedure.

Thanks 

 


Accepted Solutions
Community Manager

Re: Deleting rows before inserting into SQL Server 2016

You need to think about this in a slightly different way. Try using a tMSSQLRow and just use a query like this....

 

DELETE FROM  [Database].[dbo].[Table] 
WHERE ThatDate > (SELECT TOP 1 ThisDate, ID
FROM [Database].[dbo].[Table]
WHERE ThisDate < CONVERT(DATE, GETDATE()-30)
ORDER BY ThisDate DESC, ID DESC)

You can parameterise your query however you want using context variables or the globalMap. 

 

Alternatively (if the data comes from a different db to the db having data deleted), you could load your found date into a globalMap (as you mentioned) and then use it in your delete, again in a tMSSqlRow. 


All Replies
Community Manager

Re: Deleting rows before inserting into SQL Server 2016

You need to think about this in a slightly different way. Try using a tMSSQLRow and just use a query like this....

 

DELETE FROM  [Database].[dbo].[Table] 
WHERE ThatDate > (SELECT TOP 1 ThisDate, ID
FROM [Database].[dbo].[Table]
WHERE ThisDate < CONVERT(DATE, GETDATE()-30)
ORDER BY ThisDate DESC, ID DESC)

You can parameterise your query however you want using context variables or the globalMap. 

 

Alternatively (if the data comes from a different db to the db having data deleted), you could load your found date into a globalMap (as you mentioned) and then use it in your delete, again in a tMSSqlRow. 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

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

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch