Last post Mar 03, 2011 07:48 PM by nehaavishwaa
Mar 03, 2011 11:21 AM|shamsam|LINK
Using SQL server and C# to build my web site. I need to do a bulk upload Employee data from Excel to SQL.
I use the following code to perform this. The code works fine.
public DataSet myDataSet;
public ReadExcel(string strFileName)
string strFilePath = string.Concat("c:/BulkUpload/", strFileName);
myDataSet = new DataSet("Excel_DataSet1");
myConnectionString = "provider=Microsoft.ACE.OLEDB.12.0;data source='" + strFilePath + "';Extended Properties=Excel 12.0;";
myConnection = new OleDbConnection(myConnectionString);
myAdapter = new OleDbDataAdapter("select * from [sheet1$]", myConnection);
DataTable dt = myDataSet.Tables;
int count = 0;
foreach (DataRow r in dt.Rows)
if (count != 0)
DBConnect db = new DBConnect();
db.AddData("INSERT INTO Employee (EmployeeName,Location,Role,Certified) VALUES ('" + r.ToString() + "','" + r.ToString() + "','" + r.ToString() + "','" + r.ToString() + "')");
In the Employee table there is a field called ID which is of type int and auto incrememntral . This is the primary key.
There cannot be duplicates in the combination of EmployeeName
and Location. If the user tries to upload ( through the Excel file) the combination of
EmployeeName and Location which is there in the database, I need to flag it.
How can I identify the duplicates in an easy way rather than going through all the records again and again? Any functaion in SQL I can use to identify the duplicates?
Mar 03, 2011 11:46 AM|che3358|LINK
Try to add a statement to check exist before INSERT, something likes
IF NOT EXISTS (SELECT ID FROM Employee WHERE EmployeName = AND Location = )
INSERT INTO Employee
Mar 03, 2011 12:18 PM|shamsam|LINK
Thanks for your comments.
As per your suggestion we need to check each uploading record. Correct?
Any other efficient way we can do this? I'm just worried about the time factor it will take to upload...
Mar 03, 2011 01:43 PM|che3358|LINK
Another way: you set up both EmployeeName and Location as Promary Key. Then, when your query runs insert, DB will refuse insert, but will give the error. In your app, you just ignore the error, and goto the next.
Or, you can put IF NOT EXISTS and INSERT in a StoredProcedure, that will be very fast even though each record needs to be checked.
Mar 03, 2011 07:48 PM|nehaavishwaa|LINK
declare @table table (EID int, location varchar(20),general_field int)
insert into @table values ( 1,'arizona',13)
insert into @table values (2,'denver',21)
insert into @table values ( 3,'Seatle',15)
insert into @table values ( 4,'vegas',51)
insert into @table values (1,'arizona',67)
insert into @table values (6,'Maine',96)
insert into @table values (7,'georgia',71)
declare @insert_table table(EID int, location varchar(20),general int)
select EID,location,general_field from(
select *,row_number() over(partition By EID,location order by EID,location desc) as r
from @table)A where A.r=1
This will not allow any dplicates to be inserted. If you want to flag the dups you can take out records where A.r=2
Hope this answer your question
Please mark as answer if this solves your problem