Error: Invalid Object for Global Temporary Table in SQL Server 2012

bcpsql-server-2012stored-procedurestempdb

I'm new to stored procedure & my application requirement is to collect data in global temporary table & bulk copy using bcp.

I'm calling stored procedure, which copies data into global temporary table. When I execute stored procedure it throws an error

Invalid object name 'tempdb..qaw_temp'

Code for stored procedure:

CREATE table tempdb..qaw_temp
  (id      char(12),
   i_test    int))

print 'Before Insert'
  insert into tempdb..qaw_temp
  values('0411',
    3077,

print 'After Insert'

This work's if we execute code from Aqua Data Studio. But When I get executed in the stored procedure, it throws above mentioned error.

Stored procedure code

create PROC Xtest
as 
Begin
 << Code >>
End

Why does the stored procedure execution fail?

Please help, thank you.

Best Answer

There are 2 types of temporary tables in SQL Server. Local and Global. Differenence between these 2 are:-

1. Local temporary table starts with #, Global temporary table starts with ##

2. Local temporary table exists for a session only. Global temporary table alwyas exists in tempdb.

3. Because life of local temporary table is within a session so only the session can query on that. Multiple session can query Global temporary table, but data is limited to a session.

Here in you post you are using table to insert value (Not global or local temporary table).

Try bellow query.

CREATE table ##qaw_temp
  (id      char(12),
   i_test    int)

print 'Before Insert'
  insert into ##qaw_temp
  values('0411',
    3077)

print 'After Insert'

Thanks

Related Question