Here’s are two quick ways to Delete data in one table based on another table. This works perfectly in an ETL job where you have a staging table that indicate whether to Insert, Update, or Delete the data in the other table.
The first one uses an inner join, the second uses a simple EXISTS clause.
DELETE b
FROM [dbo].[TABLE_1] b
INNER JOIN [dbo].[TABLE_2] w
ON b.[ROW_ID] = w.[ROW_ID]
WHERE w.[CTRL_CD] = 'D'
DELETE b
FROM [dbo].[TABLE_1] b
WHERE EXISTS (
SELECT 1
FROM [dbo].[TABLE_2] w
WHERE b.[ROW_ID] = w.[ROW_ID]
AND w.[CTRL_CD] = 'D')
VN:F [1.5.8_856]
Rating: 4.8/5 (4 votes cast)
VN:F [1.5.8_856]
The other day I was trying to figure out how to send an email via Script Task in SSIS. For some reason, the Send Email Task wasn’t working for me (still can’t figure out why). Anyway, here’s what I ended up with.
Works like a charm…
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net
Public Class ScriptMain
Public Sub Main()
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
myHtmlMessage = New MailMessage(”email@somewhere.com”, “email@somewhereelse.com”, “Subject”, “Body”)
mySmtpClient = New SmtpClient(”SMTP”)
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
VN:F [1.5.8_856]
Rating: 2.5/5 (2 votes cast)
VN:F [1.5.8_856]
Recent Buzz