0

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

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
learner123
  • 37
  • 1
  • 7
  • 1
    Quite the unholy mix of variables, temp tables and loops you've got going on there. As a learning exercise, the focus should be on avoiding loops like these, since you'll be using cursors before you know it, and that way lies madness. You're better off learning about things like number (tally) tables to perform an insert like the above in a single query rather than a loop, and common table expressions (CTEs) to avoid creating tables (unless this is necessary for performance) and thereby increase maintainability. – Jeroen Mostert Feb 15 '22 at 13:58
  • `SELECT INTO` is only used to create a new table. It can't be used to insert data into an existing table. Create the table explicitly before the loop. Also try to avoid using loops. In real scenarios a loop is almost always the slowest possible way to handle data – Panagiotis Kanavos Feb 15 '22 at 14:06
  • If you need a cursor, then just use one. This common but overused (and often blindly applied) pattern of is just a poor and more error-prone substitute. As already suggested, "looping" is something you don't do often in well-written SQL applied to a well designed schema. So exactly what are you trying to learn? – SMor Feb 15 '22 at 14:31
  • I know you want to learn to loop, but just so you know: what you're trying to do here in no way indicates that a loop is warranted. Selecting directly into the temp table will get it all done in one, simple transaction. SQL is record-set based. Performance gets worse when working Row By Agonizing Row. – Brian Stork Feb 15 '22 at 17:31

0 Answers0