How to avoid data repetition insertion?

Question

Recently I have posted a question, it contains some syntax error, now the code is running without error, thanks to @Arulkumar.

But now I am facing one more problem, data from excel sheet is storing properly on to SQL Server database, but when I press refresh button or if I go to that link again in my application, data is repeating in the database. Means again it is retrieving values from excel and storing same data again in the database.

How can I avoid data repetition. Can any one please help me to solve this issue? Code and excel sheet sample is there in the above mentioned link.

Answer 1

You need MERGE statement

request.query('MERGE [mytable] as target USING (SELECT SalesPersonID, TerritoryID FROM OPENROWSET('  + 
        '\'Microsoft.ACE.OLEDB.12.0\', \'Excel 12.0;Database=D:\\sample\\test\\data\\1540_OPENROWSET_Examples.xls;HDR=YES\', ' + 
        '\'SELECT SalesPersonID, TerritoryID FROM [SELECT_Example$]\')' +
        ' ) as source' +
        ' ON target.SalesPersonID = source.SalesPersonID' +
        ' WHEN MATCHED THEN UPDATE SET TerritoryID = source.TerritoryID' +
        ' WHEN NOT MATCHED THEN INSERT (SalesPersonID, TerritoryID) VALUES (source.SalesPersonID, source.TerritoryID);'
        ,function(err,recordset){
    if(err) console.log(err)

It will update TerritoryID if there is already row with same SalesPersonID and insert row if there is no matches in mytable.

If you need join on both fields change this:

ON target.SalesPersonID = source.SalesPersonID

On this:

ON target.SalesPersonID = source.SalesPersonID AND target.TerritoryID = source.TerritoryID

And after that - remove this string because it doesn't need anymore:

'WHEN MATCHED THEN UPDATE SET TerritoryID = source.TerritoryID' +