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

I have the following problem when inserting some values coming from a subquery.

插入来自子查询的某些值时出现以下问题。

I want to insert all values from this query:

我想插入此查询中的所有值：

(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))

Do you know where could I find the problem?

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

2 个解决方案

#1

Try removing VALUES :

尝试删除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)

#2

You want insert . . . select. However, you don't need a 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;

In addition, you should include the TABLE_SCHEMA in your query (two tables can have the same name in different schema.

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

#1