SQL Server：当子查询未与EXISTS一起引入时，只能在选择列表中指定一个表达式

Question

插入来自子查询的某些值时，存在以下问题。

我想从这个查询插入所有的值：

(SELECT INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME + FORMAT(convert (datetime,date) , 'ddMMyyyy') +'EUR'+'0' as devID , 'XXX' as dev2 from kat.[dbo].[Conversions] LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'Conversions' and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 3) 

What this query returns is the following data (and more rows with the following format): Columns (devID & dev2) 
devID // dev2 
YYY05102017XXX0 // EUR 
XXX04102017XXX0 // EUR 
ZZZ03102017XXX0 // EUR 

This contains the data I want to insert but when inserting into a view: I receive the following error: 

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. 

This is the query to insert the values: 

insert into ConversionsTable(devID, dev2) values (
(SELECT INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME + FORMAT(convert (datetime,date) , 'ddMMyyyy') +'EUR'+'0' as devID , 'XXX' as dev2 from kat.[dbo].[Conversions] LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'Conversions' and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 3)) 

你知道在哪里可以找到我的问题吗？

Answer 1

尝试删除VALUES：

insert into ConversionsTable(devID, dev2) 
SELECT INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME + FORMAT(convert (datetime,date) , 'ddMMyyyy') +'EUR'+'0' as devID , 'XXX' as dev2 
from kat.[dbo].[Conversions] 
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS 
on (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'Conversions' 
and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 3) 

Answer 2

你想insert . . . select。但是，您不需要left join。

insert into ConversionsTable(devID, dev2) 
    select cols.COLUMN_NAME + FORMAT(convert(datetime, c.date), 'ddMMyyyy') + 'EUR' + '0' as devID, 
      'XXX' as dev2 
    from kat.[dbo].Conversions c join 
     INFORMATION_SCHEMA.COLUMNS cols 
     on cols.TABLE_NAME = 'Conversions' and c.ORDINAL_POSITION = 3; 

此外，你应该在你的查询TABLE_SCHEMA（两个表可以有不同的模式相同的名称。