Where clause with insert statement

Question

Hi All,

Is it possible to use where clause with insert statement. In my scenario im not getting how to use update query as the subquery returns more than one values. My query goes like this.

INSERT EntityAttributeValue
       (
              EntityID
       )
SELECT ID
FROM   (SELECT ID
       FROM    (SELECT  ID,
                        ROW_NUMBER() over(ORDER BY ID) Row
               FROM     Item
               )
               x
       WHERE   Row BETWEEN 1 AND     400
       )
       x
WHERE  Value='10KT Gold'

What i want here to do is, I have EntityID and Value column in EntityAttributeValue table and the Other table is Item table which contains ID's. So joining goes this way Item.ID = EntityAttributeValue.EntityID. Now, i want to Insert bulk ItemID values from Item table into EntityAttributeValue table where values = '10KT Gold' .

Please let me know.

Answer 1

Is this what you need?

INSERT EntityAttributeValue
       (
              EntityID
       )
SELECT ID
FROM   (SELECT  ID,
                ROW_NUMBER() over(ORDER BY ID) Row
       FROM     Item
       WHERE    Value='10KT Gold'
       )
       x
WHERE  Row BETWEEN 1 AND    400

Answer 2

There are couple of problems

1. You use x two times as table names. Even if it works, it will confuse anyone. Use a new name every time.

2. Add the table name before the column name to make clear what comes from where.

3. Where does value come from? It's not in the result set of any query that you execute.

[EDIT] This query should work:

INSERT EntityAttributeValue
   (
          EntityID
   )
SELECT x2.ID
FROM   (SELECT x1.ID
   FROM    (SELECT  i.ID,
                    ROW_NUMBER() over(ORDER BY i.ID) Row
           FROM     Item i
           )
           x1
   WHERE   Row BETWEEN 1 AND     400
   )
   x2
   join EntityAttributeValue eav on x2.ID = eav.EntityID
WHERE  eav.Value='10KT Gold'

but the result is probably not what you want because it will only select items which are already in EntityAttributeValue.