Inserting rows in a table from another table using a third table

Question

I have 3 tables in my database as shown below: enter image description here

I want to insert data in RESULT table from Infos table. And only those rows are inserted that are listed in Picker table. But since InfoID column of Result table is a primary key, so it will restrict already existed InfoID to be reinserted again. And Picker table's 'InfoIDs' can have repeated values. Picker table is actually a temporary table created in a stored procedure.

I am not so pro in making join queries, so I took help of some SO questions and developed following query:

INSERT INTO @Result([INFOID],[VALUE],[PROCESSED]) (SELECT T1.Id,
      CASE 
         WHEN T1.Value1 = '-1' THEN T1.Value2
         ELSE T1.Value1
      END, 0 
    FROM [Infos] T1 LEFT JOIN (SELECT [InfoIDs] FROM Picker AS A1 WHERE (NOT EXISTS (SELECT [INFOID] FROM @Result AS A2 WHERE A1.[InfoIDs] != A2.[INFOID]))) T2 ON T1.Id = T2.InfoIDs)

But the problem is that I am getting only one row in output of Result table. Please tell me what's wrong with my query?

UPDATE

Expected Result:

enter image description here

I tested your code and it is giving desired result. Check this sql fiddle : sqlfiddle.com/#!6/36a2d/1 — Deep
– Deep, Commented May 13, 2015 at 9:02

Tobsey · Accepted Answer · 2015-05-13 09:01:44Z

I haven't tested this but it should work. First you get the distinct values from Infos and then you merge them into the result table. If the InfoID exists already in result, then update the value. If it doesn't exist then add it. If you don't want to update existing records and only add new ones, remove the WHEN MATCHED THEN UPDATEblock, up to but excluding WHEN NOT MATCHED THEN UPDATE

MERGE INTO 
    Result
USING
    (SELECT
        DistinctInfos.ID,
        CASE WHEN DistinctInfos.Value1= -1 THEN DistinctInfos.Value2 ELSE DistinctInfos.Value1 END ValueToInsert
    FROM
        (SELECT
            Infos.ID,
            MAX(Infos.Value1) Value1,
            MAX(Infos.Value2) Value2
        FROM
            Infos
            INNER JOIN Picker ON Infos.ID = Picker.InfoIDs
        GROUP BY 
            Infos.ID
        ) DistinctInfos
    )InfosToBeInserted
ON
    (Result.InfoID = InfosToBeInserted.ID)
WHEN MATCHED THEN UPDATE
    SET Result.Value = InfosToBeInserted.ValueToInsert
WHEN NOT MATCHED THEN INSERT
    (
        InfoID,
        Result,
        Processed
    )
    VALUES
    (
        InfosToBeInserted.ID,
        InfosToBeInserted.ValueToInsert,
        0
    )

Kartic · Accepted Answer · 2015-05-13 09:53:51Z

1

Can we do something like:

INSERT INTO @Result(InfoID, Value, Processed)
(
    SELECT DISTINCT
        inf.Id,
        CASE WHEN inf.Value1 = '-1' THEN inf.Value2 ELSE inf.Value1 END,
        0 
    FROM Infos inf
    INNER JOIN Picker pic ON inf.Id = pic.InfoIDs
    WHERE NOT EXISTS (SELECT 1 FROM @Result res WHERE res.InfoID = pic.InfoIDs)
)

edited May 13, 2015 at 9:53

answered May 13, 2015 at 9:09

Kartic

2,9835 gold badges26 silver badges43 bronze badges

Comments

shA.t · Accepted Answer · 2015-05-13 10:39:59Z

1

I think you need this query:

INSERT INTO @Result
(SELECT ID, 
    CASE 
         WHEN i.Value1 = '-1' THEN i.Value2
         ELSE i.Value1
      END
Value1, 0
FROM Infos i
WHERE i.ID In (SELECT InfoIDs FROM Picker)
  AND NOT i.ID IN (SELECT InfoID FROM @Result))

answered May 13, 2015 at 10:39

shA.t

17.1k5 gold badges59 silver badges121 bronze badges

Comments

Aishwarya Shiva · Accepted Answer · 2015-05-13 10:48:54Z

1

Finally, I figured out what was actually going wrong:

INSERT INTO @Result([INFOID],[VALUE],[PROCESSED]) (SELECT T1.Id,
      CASE 
         WHEN T1.Value1 = '-1' THEN T1.Value2
         ELSE T1.Value1
      END, 0 
    FROM [Infos] T1 INNER JOIN (SELECT [InfoIDs] FROM Picker AS A1 WHERE (NOT EXISTS (SELECT [INFOID] FROM @Result AS A2 WHERE A1.[InfoIDs] = A2.[INFOID]))) T2 ON T1.Id = T2.InfoIDs)

I used INNER JOIN and in A1.[InfoIDs != A2.[INFOID] condition I added = instead of !=.

I combined all the solutions and hints suggested by the responders of this question and came up with this solution. Thank you everyone. :)

answered May 13, 2015 at 10:48

Aishwarya Shiva

3,41616 gold badges61 silver badges113 bronze badges

Collectives™ on Stack Overflow

Inserting rows in a table from another table using a third table

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related