SQL Statement Insert Into

Question

I'm getting the following no matter what I do any help would be awesome.

Msg 116, Level 16, State 1, Line 15
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Msg 109, Level 15, State 1, Line 1
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

My query

[tableA].[PROJECTID],
[tableA].[STUDYID],
[tableA].[SUBJNO],
[tableA].[CASENUMBER],
[tableA].[CASESTATUS],
[tableA].[MODIFIEDBY]
)VALUES((
SELECT b.PROJECTID, 
((SELECT TOP 1 a.STUDYID FROM [PRODVIEW] a WHERE a.DYNAME = b.DYNAME and 
a.ProjID = b.PROJID)) as STUDYID, 
b.SUBJNO, 
(b.SUBJNO + '_' + b.SEQUENCE) as CaseNumber, 
'READY' as CASESTATUS, 
b.UPLOADEDBY 
FROM [dbo].[TableB] b WHERE VIEWED = 0 
AND b.UPLOADEDDATE >=  DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)))

score 1 · Accepted Answer · answered Apr 05 '18 at 01:59

If you want to use a SELECT as the source of the data for an INSERT, then don't use VALUES, which is for inserting literal data:

INSERT INTO yourTable ([PROJECTID], [STUDYID], [SUBJNO], [CASENUMBER], [CASESTATUS],
    [MODIFIEDBY])
SELECT
    b.PROJECTID, 
    (SELECT TOP 1 a.STUDYID FROM [PRODVIEW] a
     WHERE a.DYNAME = b.DYNAME and a.ProjID = b.PROJID),
    b.SUBJNO, 
    (b.SUBJNO + '_' + b.SEQUENCE),
    'READY',
    b.UPLOADEDBY
FROM [dbo].[TableB] b
WHERE
    VIEWED = 0 AND
    b.UPLOADEDDATE >=  DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0);

There is probably a way to write your query without using a correlated subquery in the select clause, e.g. via a join. Actually, your subquery with TOP makes no sense because there is no ORDER BY clause.

Also note that you don't need to use aliases in the SELECT statement. In fact, they will just be ignored, since the INSERT determines the target columns.

@larry That depends on your definition of the word `worked`. As I mentioned, you better get an `ORDER BY` clause into your subquery. — Tim Biegeleisen, Apr 05 '18 at 02:04

SQL Statement Insert Into

1 Answers1