quote:

---

Originally posted by sqlpal2007

I have to compare the data between development table and prod production table where LanguageID = 0. Insert only the missing rows in prod table from dev table. But It is not just the INSERT. I have to insert four more rows for each insert row found. These inserts will be for LanguageID 1,2,5,6. We have respective Ltext for these 1,2,5,6 LanguageIDs in excel spreadsheet. I have to insert these rows with the Ltext and LanguageID.

For example:
If the compare result had the following row
LanguageID LText
0 Are you sure?

I have to insert four rows for this insert + above insert

LanguageID LText
1 aaaaaa(German)
2 bbbbbb(France)
...

There is a excel spreadsheet
English German France
Are you sure aaaaaa bbbbbb

Teh Ltext column will be coming from the spreadsheet

---

CREATE PROC DataInsert
AS
BEGIN
DECLARE @Temp TABLE
(
TextField varchar(2000)
)


INSERT INTO ProdTableA
OUTPUT INSERTED.LText INTO @Temp
SELECT d.LanguageID,d.LText
FROM DevTableA_Staging d (staging table with data from dev server)
LEFT JOIN ProdTableA p
ON p.PK=d.PK
WHERE p.PK IS NULL
AND d.LanguageID=0

INSERT INTO ProdTableA
SELECT 1, German
FROM Excel_Staging e
INNER JOIN @Temp t
ON  e.English=t.TextField

UNION ALL

SELECT 2, France
FROM Excel_Staging e
INNER JOIN @Temp t
ON  e.English=t.TextField

UNION ALL
...


END
GO