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)
1 ACCEPTED SOLUTION

Accepted Solutions
Eleven Stars

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. 

Rilhia Solutions
1 REPLY
Eleven Stars

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. 

Rilhia Solutions