Adding rows based on a condition sql

You can create a subquery that contains the itemId and Content of each Lang='EN' record, cross joining that on a distinct list of languages to get a list of all potential replacement records.

From there, you can a combination of a RIGHT OUTER JOIN and COALESCE to determine what language records are missing for each itemId, and replace them.

-- Create our temp table for testing, 'cause I'm too lazy to fiddle
SELECT 0 as translationId, 0 as ItemId, 'FR' as Lang, 'Livre' as Content
INTO #Test
UNION SELECT 1, 0, 'DE', 'Buch'
UNION SELECT 2, 0, 'EN', 'Book'
UNION SELECT 3, 0, 'NL', 'Boek'
UNION SELECT 4, 1, 'FR', 'Stylo'
UNION SELECT 5, 1, 'EN', 'Pen'
UNION SELECT 6, 2, 'FR', 'Plaque'
UNION SELECT 7, 2, 'NL', 'Plaat'
UNION SELECT 8, 2, 'EN', 'Plate'

-- Query starts here, replace #Test with your table name
SELECT  
    COALESCE(T1.itemId, T2.itemId) AS itemId,
    COALESCE(T1.Lang, T2.Lang) AS Lang,
    COALESCE(T1.Content, T2.Content) AS Content
FROM    #Test T1
RIGHT OUTER JOIN (
    /*
    Create a subquery that contains the Content and itemId records
    for each Lang='EN' record; we'll RIGHT OUTER JOIN on this
    */  
    SELECT itemId, Languages.Lang, Content
    FROM #Test
    CROSS JOIN (
        -- Get our distinct list of languages
        SELECT DISTINCT Lang
        FROM #Test
    ) Languages
    WHERE #Test.Lang = 'EN'
) T2 ON T1.itemId = T2.itemId and T1.Lang = T2.Lang
ORDER BY COALESCE(T1.itemId, T2.itemId)

DROP TABLE #Test  -- Clean up our temp table