Insert Values from Table Variable into already EXISTING Temp Table

Question

I'm successfully inserting values from Table Variable into new (not yet existing table) Temp Table. Have not issues when inserting small number of rows (eg. 10,000), but when inserting into a Table Variable a lot of rows (eg. 30,000) is throws an error "Server ran out of memory and external resources). To walk around the issue: I split my (60,000) Table Variable rows into small batches (eg. 10,000) each, thinking I could insert new data to already existing Temp Table, but I'm getting this error message:

There is already an object named '##TempTable' in the database.

My code is:

USE MyDataBase;
Go

Declare @@TableVariable TABLE
(
[ID] bigint PRIMARY KEY,
[BLD_ID] int NOT NULL
-- 25 more columns
)
Insert Into @@TableVariable VALUES
(1,25),
(2,30)
-- 61,000 more rows

Select * Into #TempTable From @@TableVariable;
Select Count(*) From #TempTable;

Below is the error message I'm getting

Remember @@Tables are stored in memory, so if you have a message "OutOfmemory" maybe you don't have enough memory. I suggested create a pyhiscal tempTable (with no @@Table) if you can and check if you receive the same message. — Beto, May 06 '16 at 22:59
Do not have permission to create anything... By an any chance do you know if I can add new data to an existing TempTable from Table Variable source? — Data Engineer, May 07 '16 at 00:51

score 0 · Answer 1 · answered Jun 16 '16 at 16:05

The problem is that SELECT INTO wants to create the destination table, so at second run you get the error.

first you have to create the #TempTable:

/* this creates the temptable copying the @TableVariable structure*/
Select * 
Into #TempTable 
From @TableVariable
where 1=0;

now you can loop through your batches and call this insert as many times you want..

insert Into #TempTable 
Select * From @TableVariable;

pay attention that #TempTable is different from ##TempTable ( # = Local, ## = Global ) and remember to drop it when you have finished.

also you should NOT use @@ for you table variable, use only @TableVariable

I hope this help

Insert Values from Table Variable into already EXISTING Temp Table

1 Answers1