From Thursday, July, 9, 3:00 PM Pacific,
our Community site will be in
read-only mode
through Sunday, July 12th.
Thank you for your patience.

Deleting rows before inserting into SQL Server 2016

Highlighted
Six 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
Highlighted
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. 

View solution in original post


All Replies
Highlighted
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. 

View solution in original post

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog