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

Question

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?

Remove the VALUES clause from your insert and do directly INSERT INTO ... SELECT ... — Bill Tür stands with Ukraine
– Bill Tür stands with Ukraine, Commented Oct 17, 2017 at 10:54

sagi · Accepted Answer · 2017-10-17 10:55:44Z

2

Try removing 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)

answered Oct 17, 2017 at 10:55

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2017-10-17 11:00:05Z

1

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.

answered Oct 17, 2017 at 11:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related