使用来自 UDF 的结果集插入表变量

2021-09-10 00:00:00 sql tsql sql-server

我有一个 UDF，它采用逗号分隔的列表并将其转换为行所以

I have a UDF which takes a comma separated list and turns it into rows So the output of

select * from fnDrugSplit('one,two,three',',')

会

one
two
three

当我尝试将这些结果插入到表变量中时

When I try to insert these results into a table variable with

declare @drugName1 table(drugName1 varchar(50),drugName2 varchar(50))
insert into @drugName1(drugName1,drugName2)
values(
         (select * from fnDrugSplit('one,two,three',',')
        ,(select * from fnDrugSplit('one,two,three',',')
      )

我发现 ')' 附近的语法不正确，最后一个括号将 values 块关闭.该函数将是确定性的，我不知道为什么会收到此错误，因为

I get Incorrect syntax near ')', the last parentheses closing out the values block. The function will is deterministic and I don't know why I'm getting this error because 

declare @drugName1 table(drugName1 varchar(50),drugName2 varchar(50))
insert into @drugName1(drugName1,drugName2)
values(
         (select 'one')
        ,(select 'two')
      )
select * from @drugName1

工作正常.我在这里错过了什么?

works fine. What am I missing here?

函数中的第二个参数是行的分隔符.SQL Server 2008

The second parameter in the function is the delimiter for rows. SQL Server 2008

推荐答案

您的 udf 返回一个包含 3 行的表.您不能使用VALUES"子句将 1 列的 3 行放入表中.VALUES"子句需要标量，这就是Select 'one'"和Select 'two'"起作用的原因.

Your udf returns a table with 3 rows. You can't put 3 rows of 1 column into the table with the "VALUES" clause. The "VALUES" clause expects scalars, which is why "Select 'one'" and "Select 'two'" work.

您不需要VALUES"，您可以明确表达您的选择.

You don't need "VALUES" you can just articulate your select. 

Insert into @drugName1
(drugName1, drugName2)
 select fn.ColName, fn.ColName
 from fnDrugSplit('one,two,three',',') fn

不确定如何将 3 个值放入 2 列，这在您的问题中不清楚.另外，我不知道您的 UDF 的列名是什么，所以我假设了 ColName.

Not sure how you want to put 3 values into 2 columns, that wasn't clear in your question. Also, I don't know what the column name is for your UDF, so I assumed ColName.

相关文章