CODEWITHSUNDEEP
sql-serverdatabaset-sqldatatablein-memory-tables
HillOfBeans
HillOfBeans

Reputation: 109

Querying into a memory table

SQL Server lets you create in-memory tables. But how do you do insert into operation on that?

So for example, I used this code to create my type:

CREATE TYPE dbo.typeTableDelimetedFileSpec  
       AS TABLE  
          (  
               TemplateId INT NOT NULL,  
               FieldName VARCHAR(50) NOT NUL,
               FieldPosition SMALLINT NOT NULL INDEX fpos
          )  
          WITH (MEMORY_OPTIMIZED = ON);

Then, I tried to do this:

DECLARE @T [dbo].[typeTableDelimetedFileSpec]

SELECT * 
INTO @T 
FROM [dbo].[_DelimetedFileSpec] 
WHERE TemplateId = 1

I know the structures match (_DelimetedFileSpec does not have index fpos, but other than that there are no differences).

I get: Incorrect syntax near '@T'.

Also, just to check out that there are no other errors, I confirmed that the following works fine:

SELECT * 
INTO #x 
FROM [dbo].[_DelimetedFileSpec] 
WHERE TemplateId = 1

Is it possible to somehow insert directly into the memory-table, like this?

Upvotes: 0

Views: 1137

Answers (1)

HillOfBeans
HillOfBeans

Reputation: 109

I found a way to do it efficiently!

Declare @DeliSpecs [dbo].[typeTableDelimetedFileSpec]
Insert into @DeliSpecs (TemplateId, FieldName, FieldPosition) Select TemplateId, FieldName, FieldPosition from _DelimetedFileSpec where TemplateId = @Id

Upvotes: 0

Related Questions