CODEWITHSUNDEEP
sql-server
Mister Epic
Mister Epic

Reputation: 16743

Can't assign result of table valued function to table variable

I have this function:

Create function [dbo].[Split]
(
    @String nvarchar(max)
)
returns @SplittedValues table
(
    Id nvarchar(50) primary key
)

In a stored procedure, I am trying to take the result of this functions invocation and insert it into a table variable:

DECLARE @SplittedValues table
(
    [Id] nvarchar(50) primary key
);
INSERT INTO @SplittedValues([Id])
VALUES (SELECT * FROM [dbo].[Split](@Commands);

I get

Incorrect Syntax near "SELECT"

DB version is SQl Server 2012

Upvotes: 0

Views: 39

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 221265

The error message is misleading. If you really wanted to use (scalar!) subselects in the INSERT .. VALUES() clause, you'd have to add additional parentheses:

INSERT INTO @SplittedValues([Id])
VALUES ((SELECT * FROM ...));

That would be the "correct" syntax. But in your case, that doesn't make sense anyway. You want to copy the outcome of the SELECT into the @SplittedValues table as a whole. So, try this instead:

INSERT INTO @SplittedValues([Id])
SELECT * FROM [dbo].[Split](@Commands);

Upvotes: 1

Andrey Korneyev
Andrey Korneyev

Reputation: 26886

Your syntax is incorrect. It should be

INSERT INTO @SplittedValues([Id])
SELECT * FROM [dbo].[Split](@Commands)

Syntax insert into table_name (columns_list) values (values_list) should be used when you have some finite values list written in your query.

But when you're inserting from resultset obtained from some select, it shold be insert into table_name (columns_list) select values from another_table

Upvotes: 0

Related Questions