Deleting rows before inserting into SQL Server 2016

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 

 

Tags (1)

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. 

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch