Best way to create In-Memory table with same columns and type as a permanent table-CodePudding

My question is the same as this old one, but creating an in-memory table instead of a temp table: Best way to create a temp table with same columns and type as a permanent table

I have tried this, because it is the accepted answer in the old question, but it does not work (I think it is because the table variable must be declared):

select top 0 *
into @mymemorytable
from myrealtable

It throws that exception:

Msg 102 Level 15 State 1 Line 2 Incorrect syntax near '@memorytable'.

I have also tried to declare the table variable as declare @mymemorytable as table();, but of course it throws an exception:

Msg 102 Level 15 State 1 Line 1 Incorrect syntax near ')'.

Is there any way to achieve it without declaring the table variable detailing all the fields and types? Which is the best way to get it?

Thank you

CodePudding user response：

I think the most you can do is something like this:

CREATE TYPE dbo.MyTableType
    AS TABLE  
    (  
            Column1  int
        ,   Column2  int
    )
GO 

DECLARE @InMemTable dbo.MyTableType
;  

INSERT INTO @InMemTable VALUES (1,1), (1,2), (2,3)
;  
SELECT * from @InMemTable
;

CodePudding user response：

There are no columns in your declaration of @table. You need to enter the structure of "myrealtable".

If you want to skip that part you need to use a temporary table instad of a table value parameter.

SELECT *
INTO #table
FROM myrealtable

Alos, it's considered good practise to drop the temporary table once your done with it. It's also considered bad practise to use SELECT * instead of listing all columns :)