Prevent duplication of row in SQL Server 2008 when importing data from excel

Question

I am importing data from Excel into a SQL Server 2008 database and I am successful in doing that. Is there any way where I can check if the current row exist in database before inserting that particular row from Excel?

Here is the code which I am using to import data from excel

<pre lang="c#">
con.Open();
if (FileUpload1.HasFile)
        {
            string path = FileUpload1.PostedFile.FileName;
            string saveFolder = @"E:\"+""+path+""; //Pick a folder on your machine to store the uploaded files
            FileUpload1.SaveAs(saveFolder);
            String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + saveFolder + ";Extended Properties=Excel 12.0;");
            OleDbConnection excelConnection = new OleDbConnection(excelConnString);
            OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(con);
            sqlBulk.DestinationTableName = "Course_Data";
            sqlBulk.WriteToServer(dReader);
            excelConnection.Close();
            con.Close();
            Response.Write("<Script> alert('File Uploaded Successfully');</Script>");
        }
        else
        {
            Response.Write("<Script> alert('First select the file which you need to upload.');</Script>");
        }
con.Close();
</pre>

Thank You.

Bulk insert is your fastest way of getting file data into the database however you could do the bulk insert into a **temporary** table and then call a stored procedure that processes the data in that table (checks for duplicates etc) and moves it into its final destination. — connectedsoftware, Feb 27 '15 at 12:34

score 0 · Answer 1 · answered Feb 27 '15 at 13:06

you can use SQL Server INSTEAD OF trigger: make sure you use the HDR set to yes to get the header as column name in excel connection string

String excelConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + saveFolder + ";Extended Properties=Excel 12.0;HDR=YES;"

or you will need to replace the column below by index(F$) replace $ with the index of excel column you want to compare

IE : you validate insert by course_name field

CREATE TRIGGER IO_Trig_INS_CourseData on Course_Data
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Course_name. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT C.ID
      FROM Course_Data C, inserted I
      WHERE P.Course_Name = I.Course_Name ))
   INSERT INTO Course_Data 
      SELECT *
      FROM inserted
END

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

You can copy the data into a DataTable and filter out the duplicates and then insert the data into DB.

See here how to load the data from DataReader to DataTable.

See here how to remove duplicates using Comparer

edited May 23 '17 at 12:06

Community

1
1

answered Feb 27 '15 at 13:11

SelvaS

2,105
1
22
31

Prevent duplication of row in SQL Server 2008 when importing data from excel

2 Answers2