Is it possible to copy both Structure and Data from a Table Variable into a Temporary Table?

Question

Hi!

Is it possible to copy STRUCTURE & DATA from one table (Table1) to a new table (Table2) created on the fly?

Yes! I can do this one.

For example:

SELECT * INTO ##Table2 from #Table1     <---- This works!

Here, #Table1 has been already defined and contains data, but ##Table2 is nowhere defined before this SELECT. And it works.

-----------------------------------

I am now wondering: Suppose I have already declared a TABLE VARIABLE called @Table1, and have populated it with data.

Is it possible to copy both STRUCTURE & DATA to a TABLE called ##Table2 (which is created on the fly here)?

Something analogous to the above code:

SELECT * INTO ##Table2 from @Table1    <--- This does not work!

How can we achieve what I want to do?

-------------------------------

REMARKS: If you wish to understand my motivation, here are a few brief notes.

I wrote a stored procedure using temporary tables.

I now want to make it run faster using table variables instead.

But, as a final step, I will have to copy everything from the table variable into a ##Table2.

But the name of ##Table2 is not fixed, and has to be generated by Dynamic SQL.

--------------------------------

Thanks

Leon Lai

Answer 1

It does work..Why do  you use global temporary table? Why not locals (#) 

 DECLARE @t TABLE (c INT)

 SELECT * INTO #t1 FROM @t

 SELECT * FROM #t1

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

This also works ?

DECLARE @t TABLE (c INT)
insert into @t select 1
SELECT * INTO ##t1 FROM @t

SELECT * FROM ##t1 

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 3

Hi Uri

Thanks a lot for your reply. It works.

The reason why I use ## instead of # is because I need to pass the results set to Crystal Reports to produce the final report.

Unfortunately, # is dropped automatically as soon as the Stored Procedure has executed. So I need to use ##.

If you have any idea how NOT to use ##, please drop me a reply on this page.

Best Regards

Leon Lai

Answer 4

Hi Eralper,

Thanks a lot for your reply. It works.

Best Regards,

Leon

Answer 5

If you have any idea how NOT to use ##, please drop me a reply on this page.

## is a global temp table, accessible across all sessions. Hence, a high concurrent system, executing the proc by many people would end up with wrong data or unexpected errors due to other seesion. For eg: User A created the ##table. By the time the other user B truncates the table. So, User A will not be having the data he loaded before. This is a very risky case.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 6

Hi Latheesh

Thanks a lot for your reply

I am aware of the risk you are mentioning.

To avoid the risk, this is what I have done:

I don't use a  ##Table with a fixed name.

Instead, I use a table name which is a concatenation of a fixed name + the Host_Name() of the user.

For example ##Table1JohnComputer

I use dynamic SQL to do the trick.

In this way I get the best of both worlds: I can use a ##Table which I can use in Crystal Report, and the table behaves like #

Best Regards

Leon