I'm sure this is easy but I'm still getting syntax errors when I run it in SQL Server Management Studio v17.4.
I have table T1 which has serial numbers, and I just need to iterate through and check how many times they appear in T2. I tried like
declare @serial int
select @serial = select serial from T1
while @serial
begin
select count(*) from T2 where Serial = @serial
end
But I get an error:
Incorrect syntax near the keyword 'select'
How to do it? Thanks.
Instead of while loop, just join the tables and use an aggregate. Something like:
select a.serial, count(a.serial) from t2 a
inner join t1 b on b.serial = a.serial
group by a.serial
Without sample data I can't test it out for you, but that will perform a lot better for you.
Simply
SELECT T1.Serial,
COUNT(T2.Serial) AppearsInT2
FROM T1 JOIN T2 ON T1.Serial = T2.Serial
GROUP BY T1.Serial
There is no need to declare a variable or to use a loop.
If you want to return 0 for serials which not exists in the second table use LEFT JOIN instead
SELECT T1.Serial,
COUNT(T2.Serial) AppearsInT2
FROM T1 LEFT JOIN T2 ON T1.Serial = T2.Serial
GROUP BY T1.Serial;
First of all, doing:
select @serial = select serial from T1
while @serial
…
Doesn't mean that it will start to magically loop for every value of serial that T1 has, it will just assign one value of serial to your variable (if it was correctly written anyway select @serial = serial from T1).
What you want doesn't really make sense to do it in a loop; sql works in sets, and you should try to write your code accordingly. In this case, a simple JOIN should do:
SELECT T1.Serial,
ISNULL(COUNT(T2.Serial),0) N
FROM T1
LEFT JOIN T2
ON T1.Serial = T2.Serial
GROUP BY T1.Serial
;
Seems like you could just do this in one quick statement, rather than a loop.
SELECT T2.Serial, Count(T2.Serial) as NumOfSerial
FROM T1
INNER JOIN T2 ON T1.Serial = T2.Serial
GROUP BY T2.Serial
Yes it is a syntax error
select @serial = select serial from T1; -- is wrong it should as written below
select @serial = serial from T1 ;
This will select the 1st value from Table T1.
This will remove the error but the query written will not yeald the required output.
You need to loop through table T1 and for each value of T1 search in T2.
See if the below simple query helps
select
serial,
count(Serial) as SerilaCount
from
T1
inner join T2 on T1.serial = T2.Serial
group by
T1.serial
SELECT COUNT(*) FROM T2 WHERE Serial = @Serial?