1

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.

Community
  • 1
  • 1
naik3
  • 319
  • 2
  • 8
  • 22

1 Answers1

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' +
gofr1
  • 15,741
  • 11
  • 42
  • 52
  • Hi , thank you for replying with line by line explanation , i tried to run code , it is giving some syntax errors : – naik3 May 12 '16 at 06:56
  • `{ [RequestError: Incorrect syntax near the keyword 'as'.] name: 'RequestError', message: 'Incorrect syntax near the keyword \'as\'.', code: 'EREQUEST', number: 156, lineNumber: 1, class: 15, serverName: 'localhost\\SQLEXPRESS', procName: '', precedingErrors: [ { [RequestError: Incorrect syntax near '('.] name: 'RequestError', message: 'Incorrect syntax near \'(\'.', code: 'EREQUEST', number: 102, lineNumber: 1, state: 1, class: 15, serverName: 'localhost\\SQLEXPRESS', procName: '' } ] }` – naik3 May 12 '16 at 06:56
  • Give me a minute, I messed up with code formatting! – gofr1 May 12 '16 at 07:06
  • Thank you for reply. I tried running updated code , now syntax error is not there , but it is showing this message : **RequestError: A MERGE statement must be terminated by a semi-colon (;).]** – naik3 May 12 '16 at 07:21
  • Just add `;` in the end of query (I add this to answer) – gofr1 May 12 '16 at 07:26
  • thank you very much.. Now the code is running properly. – naik3 May 12 '16 at 07:31