CODEWITHSUNDEEP
sqlt-sqlselectsql-insert
Danny Keeley
Danny Keeley

Reputation: 11

insert query to have both a select and a value as values

I know you can use a select statement to supply all the values, but it wont let me use it to just supply 1 of the values, then use another item to supply the next value.

INSERT INTO table
([product code], [product description])
VALUES
(
   (SELECT [code] FROM table GROUP BY [code])
   ,'a description'
)

But SQL Server tells me

Sub query returned more than 1 value

For now I have gotten around it by making that select statement a view and then joined it. Would be nice to not need a view though.

Upvotes: 0

Views: 371

Answers (3)

Shredder
Shredder

Reputation: 1

When you insert using VALUES, you can only insert one row at a time, however, the query

'SELECT [code] FROM table GROUP BY [code]'

returns multiple rows hence the query fails. If you want to use VALUES to insert you would need to loop through each row in the query and insert separately, however, this is unnecessary and inefficient. As others have already pointed out it is far more efficient to simply use the below:

INSERT INTO table ([product code], [product description])
     SELECT [code], 'a description'  
     FROM table 
     GROUP BY [code];

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You just want INSERT . . . SELECT:

INSERT INTO table ([product code], [product description])
    SELECT [code], 'a description'
    FROM table
    GROUP BY [code];

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use SELECT statement with INSERT INTO . . . statement :

INSERT INTO table ([product code], [product description])
     SELECT [code], 'a description'  
     FROM table 
     GROUP BY [code];

Upvotes: 2

Related Questions