'Looping through table variables and inserting values into Temp table throws an error there is already an object

I am trying to learn how to loop through table variables and inserting those values into temp table but it throws an error that there is already an object named even though i dropped the temp table if exists.

Create TABLE Dept(
  id int NOT NULL,
  name VARCHAR(13)
);
  
Create TABLE Student(
   id int NOT NULL,
   Fname VARCHAR(25),
   Lname VARCHAR(25)
;
  
Insert INTO Dept(id,name)
values(10,'IT'),
(20,'Admin');

Insert INTO Student(id,Fname,Lname)
values(001,'Jack','P'),
(001,'Jack','P');

DECLARE @LOOP INT
DECLARE @COUNTOFRECORDS INT
DECLARE @x TABLE(
r_no int not null primary key identity(1,1)
name VARCHAR(20))

Insert into @x select name from dept
IF OBJECT_ID(N'tempdb..#tempstudent') IS NOT NULL
BEGIN
    DROP TABLE #tempstudent
END
SELECT @LOOP = 1
SELECT @COUNTOFRECORDS = COUNT(r_no) from @x
while(@LOOP <= @COUNTOFRECORDS)
BEGIN
    SELECT s.fname,s.lname INTO #tempstudent from Student s
    select @LOOP=@LOOP + 1
END

Why it is still throwing a message that there is already an object named #tempstudent.I tried with global temp table too.Attached the fiddle http://sqlfiddle.com/#!7/b920e/1



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source