I have a server log file that I am trying to pare down for easier inspection. The table I am searching through is from just doing a bulk insert into a single column table named Spam.LogData. One big part of doing this is removing activity of legitimate users. I already have a statement that can extract the session IDs that need to be removed by parsing a particular line from the log. It's the only line that always has the session ID and email address in the same place. I can then read the session IDs into a pretty simple table variable.
declare @sessionid table (sesID int)
The problem is creating a like statement using the data from this table to find & remove the necessary lines. I thought this statement would allow me to select out the lines associated with these session IDs and by flipping the comment from the delete to the select, remove them.
--delete
select *
from Spam.LogData
where data like '%' + (select 'session ' + cast(sesID as nvarchar) from @sessionid) + '%'
However I get the error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
What's the best way to do this?
Perhaps you could use a JOIN instead of a subquery:
--delete LD
select LD.*
from Spam.LogData as LD
inner join @sessionid as S on LD.data like '%session ' + cast(S.sesID as nvarchar) + '%'