find different rows in tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

find different rows in tables

Author

Topic

kx250f37
Starting Member

8 Posts

Posted - 2012-04-30 : 14:13:18

i've started the search for different rows in two tables but I'm not sure how to complete the remaining - not sure how to loop through rows in temp table to send the emails per row

WHILE EXISTS (
SELECT *
FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
--returns 2 rows that are different
)
BEGIN
--PRINT 'changes found'
BEGIN

SELECT a.flightId,a.FlightTime,a.BlockTime,a.TTEngine1,a.TCEngine1,a.TTEngine2,a.TCEngine2,a.TTAirframe,a.TCAirframe
INTO #mytemp FROM flightsClosed as a
left JOIN flightsClosed2 b
ON a.FlightID = b.FlightID
WHERE a.StatusTypeID != b.StatusTypeID
AND a.StatusTypeID = 44
End

BEGIN
declare @flightId int
declare @flightTime DECIMAL(10,2)
declare @blockTime DECIMAL(10,2)
declare @ttEngine1 DECIMAL(10,2)
declare @tcEngine1 DECIMAL(10,2)
declare @ttEngine2 DECIMAL(10,2)
declare @tcEngine2 DECIMAL(10,2)
declare @ttAirframe DECIMAL(10,2)
declare @tcAirframe DECIMAL(10,2)
declare @profileName nvarchar(128)= 'I'
declare @To nvarchar(255) = 'to'
--declare @BCC nvarchar(255) ='bcc'
declare @From nvarchar(255) ='from'
declare @Subject nvarchar(128)= 'Flight Closed'
declare @Message varchar(2000)

--get data elements for email msg
select @flightId = flightId,
@flightTime = FlightTime,
@blockTime = BlockTime,
@ttEngine1 = TTEngine1,
@tcEngine1 = TCEngine1,
@ttEngine2 = TTEngine2,
@tcEngine2 = TCEngine2,
@ttAirframe = TTAirframe,
@tcAirframe = TCAirframe
FROM #mytemp

set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))

begin
exec msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Message
end
--@blind_copy_recipients = @BCC,
END
END
PRINT 'no changes found'

oh here is my error:

(2 row(s) affected)
Mail queued.
Msg 2714, Level 16, State 6, Line 37
There is already an object named '#mytemp' in the database.

so i'm guessing it finds both rows, then it sends one email based on the last row but doesn't do so for the 1st row.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 14:16:43

whats the purpose of LEFT JOIN in first exists statement? do you mean you want to regard NULL (absence of value) also as a difference? if yes, != wont work with NULL. you might have to put a separate condition for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kx250f37
Starting Member

8 Posts

Posted - 2012-04-30 : 14:21:00

fortunately there won't be a null value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 14:23:46

quote:
Originally posted by kx250f37

fortunately there won't be a null value.

then why use LEFT JOIN? you could have used INNER JOIN if there are matches always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kx250f37
Starting Member

8 Posts

Posted - 2012-04-30 : 14:28:46

will try the inner join - that still doesn't solve why i can send multiple emails if there are more than 1 row of data; will let you know how the inner works

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 14:32:42

where's the loop? you just have a single call to sp_send_dbmail so it will send only single mail. probably what you need is a loop to iterate over some condition and then add send mail logic inside loop

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kx250f37
Starting Member

8 Posts

Posted - 2012-04-30 : 14:35:32

so a loop at the call for send email or should it be at the get data emelments? how is that done?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 14:38:24

quote:
Originally posted by kx250f37

so a loop at the call for send email or should it be at the get data emelments? how is that done?

the stub should be like

start loop based on your iteration logic
fetch data required for this iteration
do processing if any
send mail with details
end loop

will mail be send to same recipients always? if not, that logic also has to be inside loop before send mail step

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kx250f37
Starting Member

8 Posts

Posted - 2012-04-30 : 14:45:25

how about after the declares I do the following but it doesn't like the select @flightid = flightid

DECLARE @rowcount INT
SET @rowcount = (select @flightId = flightId,
@flightTime = FlightTime,
@blockTime = BlockTime,
@ttEngine1 = TTEngine1,
@tcEngine1 = TCEngine1,
@ttEngine2 = TTEngine2,
@tcEngine2 = TCEngine2,
@ttAirframe = TTAirframe,
@tcAirframe = TCAirframe
FROM #mytemp)

DECLARE @I INT
SET @I = 1

WHILE (@I <= @rowcount)
Begin

--get data elements for email msg
--select @flightId = flightId,
-- @flightTime = FlightTime,
-- @blockTime = BlockTime,
-- @ttEngine1 = TTEngine1,
-- @tcEngine1 = TCEngine1,
-- @ttEngine2 = TTEngine2,
-- @tcEngine2 = TCEngine2,
-- @ttAirframe = TTAirframe,
-- @tcAirframe = TCAirframe
-- FROM #mytemp

set @Message = 'Flight ID: ' + Cast(@flightId as varchar(50)) + ' is now closed.'
+ CHAR(13) + CHAR(13) +
'Actual Flight: ' + Cast(@flightTime as varchar(50)) + CHAR(13) +
'Actual Block: ' + Cast(@blockTime as varchar(50)) + CHAR(13) +
'Hours End: ' + Cast(@ttAirframe as varchar(50)) + CHAR(13) +
'Landings End: ' + Cast(@tcAirframe as varchar(50)) + CHAR(13) +
'Engine 1 Hours End: ' + Cast(@ttEngine1 as varchar(50)) + CHAR(13) +
'Engine 1 Cycles End: ' + Cast(@tcEngine1 as varchar(50)) + CHAR(13) +
'Engine 2 Hours End: ' + Cast(@TTEngine2 as varchar(50)) + CHAR(13) +
'Engine 2 Cycles End: ' + Cast(@TCEngine2 as varchar(50))

begin
exec msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @To,
@from_address = @From,
@subject = @Subject,
@body = @Message
end
--@blind_copy_recipients = @BCC,
SET @I = @I + 1
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 14:48:50

that select doesnt make any sense. You should be retrieving only one row at a time so there should be a where condition. just putting select on table causes multiple rows to be retrieved and variable will be storing only one out of those multiple value which is not what you want.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kx250f37
Starting Member

8 Posts

Posted - 2012-04-30 : 15:08:12

how do i do that? is it a select at recid? why does it faile when i set the rowcount variable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 15:37:38

you're selecting individual values inside statement. i think rowcount statement should be like

DECLARE @rowcount INT
SET @rowcount = (select count(*) FROM #mytemp)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kx250f37
Starting Member

8 Posts

Posted - 2012-04-30 : 15:42:37

won't that exclude the columns needed in the email?

(2 row(s) affected)
Mail queued.
Mail queued.
Msg 2714, Level 16, State 6, Line 37
There is already an object named '#mytemp' in the database.

yup did that and nothing in emails. is it trying to create the temp table twice?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 10:11:42

is the select ... INTO statement inside loop?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources