0

I am trying to insert more than one row in a table which uses the select statement as a sub query. It works fine when I insert only one row but gives an error when I try to insert more than one row. 

INSERT INTO sysdba.ACTIVITY ([ACTIVITYID], [USERID], [CONTACTID])
    (SELECT top 1
        (SELECT CAST(MAX(ACTIVITYID) AS INT) 
         FROM sysdba.ACTIVITY) + 
        ROW_NUMBER() OVER (ORDER BY ENTITYID),
        'admin',
        [ENTITYID]
    FROM 
        sysdba.CAMPAIGNTARGET
    WHERE 
        CAMPAIGNID = 'Q6UJ9A00056A')

The error I get is:

Conversion failed when converting the varchar value 'CALENDAR' to data type int.

TT.
  • 15,774
  • 6
  • 47
  • 88
Jayanta Rijal
  • 69
  • 1
  • 8
  • 1
    I think there is one data into Activity table in ActivityID column is 'CALENDAR' .that's why error occurs . – Ronak Patel Dec 08 '16 at 09:41
  • Does the sub query run on its own? If so, do the values match expectations. If, not, the problem is probably the cast. – HoneyBadger Dec 08 '16 at 10:00
  • yes sub query run and gives the fine result. Figured out the problem, it was due to cast. And please someone suggest me to cast into user defined data type as ACTIVITYID is user defined data type – Jayanta Rijal Dec 08 '16 at 10:46
  • Glad you found your problem. If you can't find an answer to your last question, you should post a new question. – HoneyBadger Dec 08 '16 at 18:57

0 Answers0