3 Replies - 3046 Views - Last Post: 03 June 2012 - 07:56 PM Rate Topic: -----

#1 meccer  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 02-June 12

Upload excel to Sql Server checking for duplicates

Posted 02 June 2012 - 09:59 AM

Good day,

I'm developing a functionality where a user can import an excel file to sql server, I have got all of this working.

I do have a uniqueIdentifer in the database, which obviously generates an error. I need to check if the identifier exist; if it does update if not insert data.
I do not know what code to use to check if the field in excel is in the sql database, can someone please help?

Please note I am not familiar with stored procedure at all.

Is This A Good Question/Topic? 0
  • +

Replies To: Upload excel to Sql Server checking for duplicates

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4401
  • View blog
  • Posts: 12,257
  • Joined: 18-April 07

Re: Upload excel to Sql Server checking for duplicates

Posted 02 June 2012 - 11:09 AM

Well tell us more about how your tool works. Does it simply run through each row, create an insert SQL statement and insert it into SQL Server? Or are you talking about importing into SQL using something like management studio? Or are you using a stored procedure? If so, you might want to show us the stored procedure.

Ideally you can run a query which looks up records using the unique id and if it finds a record, build an Update query. If not, do the insert as normal.

:)
Was This Post Helpful? 1
  • +
  • -

#3 meccer  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 02-June 12

Re: Upload excel to Sql Server checking for duplicates

Posted 03 June 2012 - 03:11 AM

this is my code, to upload a excel spreadsheet to sql server;

// fetch data from excel file
            OleDbCommand cmd = new OleDbCommand("Select * from [" + sheetName + "]", excelConnection);
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(myConnection);

            myConnection.Open();

            // write data
            sqlBulk.DestinationTableName = "products";
            sqlBulk.WriteToServer(dReader);



Stock field is a uniqueIdentifier in the sql database, if I upload I receive this error;
Violation of PRIMARY KEY constraint 'PK_products'. Cannot insert duplicate key in object 'dbo.products'.

I am not sure how to incorporate the code to skip a record if it is already in the database.
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 3650
  • View blog
  • Posts: 11,419
  • Joined: 05-May 12

Re: Upload excel to Sql Server checking for duplicates

Posted 03 June 2012 - 07:56 PM

If you want to preserve the current code that you have, you could do something like:
class MyDataReader : IDataReader
{
    IDataReader _orig;

    public MyDataReader(IDataReader original)
    {
        _orig = original;
    }

    public override Read()
    {
        // Your code goes here to Read() from the _orig IDataReader.
        // If the row read from the original is acceptable, map all the other IDataReader calls
        // to the original. If not move on to the next row in the original.
    }
};


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1