Why it says "there is already an object named '#tmpOrderss' in the database." at line 65 when i am dropping that table before "begin"

Question

I am getting below error when tried to execute this sql.

Msg 2714, Level 16, State 1, Line 65 There is already an object named '#tmpOrderss' in the database.

use husysEQ

select distinct orderid
into #tmpOrderToCancel
from Tier2_db.dbo.OrdersToCancel_SDP1234 


create table #tmpBatchids ( batchid bigint )

declare @rows int, @batchid int
set @rows = 1



insert batches ( batchCommandId, batchStatusId, parameters, targetDate ) 
select 7, 1, '-1|buyerRemorse|1101765',  getdate()

select @batchId = scope_identity ( ) 


insert #tmpBatchids
select @batchId


select top 3 orderid
into #tmpOrderss
from #tmpOrderToCancel


insert batchrecords (batchid, externalid, batchRecordStatusId)
select  @batchId, orderid, 1
from #tmpOrderss


set @rows = @@ROWCOUNT


update batches set batchStatusId = 2 where batchid = @batchId


delete #tmpOrderToCancel where orderid in ( select orderid from #tmpOrderss )
drop table #tmpOrderss

while @rows > 0
begin


    insert batches ( batchCommandId, batchStatusId, parameters, targetDate ) 
    select 7, 1, '-1|bR|1101765',  dateadd(mi, 02, getdate()) 

    select @batchId = scope_identity ( ) 


    insert #tmpBatchids
    select @batchId


    select top 3 orderid
    into #tmpOrderss
    from #tmpOrderToCancel


    set @rows = @@ROWCOUNT


    insert batchrecords (batchid, externalid, batchRecordStatusId)
    select  @batchId, orderid, 1
    from #tmpOrderss


    update batches set batchStatusId = 2 where batchid = @batchId


    delete #tmpOrderToCancel where orderid in ( select orderid from #tmpOrderss )
    drop table #tmpOrderss

end


drop table #tmpOrderToCancel

select * from #tmpBatchids

possible duplicate of [Why does SQL Server thinks a Temp Table already exists when it doesn't?](http://stackoverflow.com/questions/4828261/why-does-sql-server-thinks-a-temp-table-already-exists-when-it-doesnt) — Brian, Jul 16 '14 at 18:57
i did check that post..but i couldn't find issue in my script.. — Ankit, Jul 16 '14 at 18:58
Because select into is used when the target does not yet exist. You are doing this twice. This process should not be using a while loop. You should read up on select into and also using OUTPUT. From what I can see this entire thing could probably be turned into a single insert statement instead of 50 lines of programming code in t-sql. — Sean Lange, Jul 16 '14 at 19:00
@SeanLange with 1st select into i m creating temp table and putting some data in..and then using that table to push data..before using another select into i am dropping the 1st table..so that shouldn't be creating problem, right? — Ankit, Jul 16 '14 at 19:09
Please google "RBAR sql server".......this is procedural code... — granadaCoder, Jul 16 '14 at 19:09
If you stick with this approach (which I already suggested this need a complete rewrite) you would need to use an insert from for the second set of data. You can't use select into a table that already exists. You would change the second insert to this. INSERT #tmpOrderss(orderid) select top 3 orderid from #tmpOrderToCancel — Sean Lange, Jul 16 '14 at 19:14
Please note...you are selecting top 3 with no order by. That indicates that you don't care what order these are returned. You are saying "give me any 3 rows". This is an extremely important concept and I suspect that you actually care which 3 rows you are inserting. — Sean Lange, Jul 16 '14 at 19:16
GranadaCoder - Yes, this is RBAR but there might be some business reason behind it. Given small record sets, it might be fine for performance. @SeanLange my solution uses a row number since order is not guaranteed with TOP. Ankit - I used a table variable and insert into, better than SELECT INTO. — CRAFTY DBA, Jul 16 '14 at 19:56
Curious why you say that a table variable is better than select into. They both are viable options depending on the situation. A blanket statement like that is likely to misunderstood and carried into another situation where a select into is the best choice. — Sean Lange, Jul 16 '14 at 21:00

score 1 · Answer 1 · answered Jul 16 '14 at 20:22

1

From CREATE TABLE (SQL Server)

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

answered Jul 16 '14 at 20:22

Mikael Eriksson

136,425
22
210
281

score 0 · Answer 2 · answered Jul 16 '14 at 19:54

Try out this solution, I do not have a test environment; so there could be a syntax error.

QUICK ALGORITHM FOR PROBLEM

1 - Create table variable

Keep track of row number
Actual order id to cancel
Batch id is associated with 1 .. 3 orders

2 - Create a batch master record

3 - Create up to three batch detail records

4 - Update the batch master status to complete

5 - Mark off the three orders from the table variable

6 - While there are un marked records, repeat steps 2 to 5.

-- Use correct database
use husysEQ
GO


-- Table variable 1
declare @Orders2Cancel table
(
    rownum int identity(1,1),
    orderid int,
    batchid int default -1
);

-- Variable 2
Declare @batchid int = 0;


-- Get a complete list of order ids
insert into @Orders2Cancel (orderid)
select distinct orderid
from Tier2_db.dbo.OrdersToCancel_SDP1234;


-- Add 2 batch table
insert into batches ( batchCommandId, batchStatusId, parameters, targetDate ) 
select 7, 1, '-1|buyerRemorse|1101765',  getdate();


-- Get batch id value (autoincrement)
select @batchid = scope_identity();


-- Add 2 batch records table
insert batchrecords (batchid, externalid, batchRecordStatusId)
select top 3 
    @batchId, orderid, 1
from @Orders2Cancel 
where batchid = -1
order by rownum;


-- Flip status flag
update batches 
set batchStatusId = 2 where batchid = @batchId;


-- Update with used batch ids.
update @Orders2Cancel 
set batchid = @batchid
where orderid in
(
    select top 3 orderid
    From @Orders2Cancel 
    Where batchid = -1
    order by rownum
);


-- While there are orders to cancel
while (select count(*) from @Orders2Cancel where batchid = -1) > 0
begin

   -- Add 2 batch table
   insert into batches ( batchCommandId, batchStatusId, parameters, targetDate ) 
   select 7, 1, '-1|bR|1101765',  dateadd(mi, 02, getdate()) ;


   -- Get batch id value (autoincrement)
   select @batchid = scope_identity();


   -- Add 2 batch records table
   insert batchrecords (batchid, externalid, batchRecordStatusId)
   select top 3 
      @batchId, orderid, 1
   from @Orders2Cancel 
   where batchid = -1
   order by rownum;


   -- Flip status flag
   update batches 
   set batchStatusId = 2 where batchid = @batchId;


   -- Update with used batch ids.
   update @Orders2Cancel 
   set batchid = @batchid
   where orderid in
   (
        select top 3 orderid
        From @Orders2Cancel 
        Where batchid = -1
        order by rownum
   );

end;


-- Return the batch numbers
select distinct batchid from @Orders2Cancel;

This is better but still using a loop. From the code posted there is no reason for a loop. Also, I would be a bit nervous about using table variables. They have their place but unless this is a really small dataset a temp table is far more likely to perform better. — Sean Lange, Jul 16 '14 at 20:10
The table variable is keeping a list of order and batch numbers with a row number for ordering. A table variable will reside in memory if there is plenty available or in [tempdb]. I have seen and used this type of loop to keep log file sizes down when inserting, updating or deleting large amounts of data. If you do it in one big transaction, all data has to be Write Ahead Logging (WAL) before saving to disk. — CRAFTY DBA, Jul 16 '14 at 20:50
Also, instead of using scope_identity(), I would look into using a OUTPUT clause to return the batchid. — CRAFTY DBA, Jul 16 '14 at 20:51
Agreed about using a loop for keeping batch sizes small so the log doesn't blow out. The problem with a table variable here is that is could be quite large and it being queried repeatedly. There are no indexes and has no statistics so this could be problematic if there is a lot of data. — Sean Lange, Jul 16 '14 at 20:56
Very good points @SeanLange. However, we can add primary keys and unique constraints to the table variable to speed up the loop. Again, this is version 1.x and it is up to the user to perfect it. — CRAFTY DBA, Jul 17 '14 at 13:48
I can't use the script provided above as there are thousands of orders to update...if i update all at once then it might cause issue in production..i am not a sql expert, need help in understanding 1 thing: before the 2nd select into i am dropping temp table so why sql is getting failed? — Ankit, Jul 17 '14 at 16:43
You are inserting and deleting from three temporary tables ( tmpBatchids, tmpOrderss, tmpOrderToCancel). Alot of extra work. Since there is no batch terminator (GO), the batch follows the rules Mikael has pointed out from books online. In My Own opinion (IMOO), I like one table variable that is inserted into once and selected from and/or updated. — CRAFTY DBA, Jul 17 '14 at 21:42

Why it says "there is already an object named '#tmpOrderss' in the database." at line 65 when i am dropping that table before "begin"

2 Answers2