check existing record before inserting

Question

I want to insert multiple records (~1000) using C# and SQL Server 2000 as a datatabase but before inserting how can I check if the record i'm inserting already exists and if so the next record should be inserted. The records are coming from a structured excel file then I load them in a generic collection and iterate through each item and perform insert like this

// Insert records into database
private void insertRecords() {

  try {
    // iterate through all records 
    // and perform insert on each iteration
    for (int i = 0; i < names.Count; i++) {
      sCommand.Parameters.AddWithValue("@name", Names[i]);
      sCommand.Parameters.AddWithValue("@person", ContactPeople[i]);
      sCommand.Parameters.AddWithValue("@number", Phones[i]);
      sCommand.Parameters.AddWithValue("@address", Addresses[i]); 

      // Open the connection
      sConnection.Open();
      sCommand.ExecuteNonQuery();
      sConnection.Close();

    }
  } catch (SqlException ex) {
    throw ex;
  }
}

This code uses a stored procedure to insert the records but I can check the record before inserting?

score 4 · Answer 1 · answered Feb 16 '11 at 15:02

4

Inside your stored procedure, you can have a check something like this (guessing table and column names, since you didn't specify):

IF EXISTS(SELECT * FROM dbo.YourTable WHERE Name = @Name)
     RETURN

-- here, after the check, do the INSERT

You might also want to create a UNIQUE INDEX on your Name column to make sure no two rows with the same value exist:

CREATE UNIQUE NONCLUSTERED INDEX UIX_Name
   ON dbo.YourTable(Name)

answered Feb 16 '11 at 15:02

marc_s

732,580
175
1,330
1,459

SO I looked into inserting many records as a batch and I found about using SQLDataAdapter to excecute multiple inserts without having to open and close the connection each time. The code doesn't reports that it has successfully worked but the new records are not the database and I don't what's causing it. this is my code – – Henry Feb 17 '11 at 06:51
`code string sql = "SELECT * FROM DEBTOR"; // Create data adapter SqlDataAdapter da = new SqlDataAdapter(sql, ConnectionString); // Create and fill dataset DataSet ds = new DataSet(); da.Fill(ds, "debtor"); // Get data table reference DataTable dt = ds.Tables["debtor"]; sCommand.UpdatedRowSource = UpdateRowSource.None; SqlDataAdapter adpt = new SqlDataAdapter();` -- continued – Henry Feb 17 '11 at 06:52
for (int i = 0; i < names.Count; i++) { sCommand.Parameters.AddWithValue("@debtor_name", Names[i].ToString()); sCommand.Parameters.AddWithValue("@contact_person", ContactPeople[i].ToString()); sCommand.Parameters.AddWithValue("@contact_number", Phones[i].ToString()); sCommand.Parameters.AddWithValue("@contact_address", Addresses[i].ToString()); } adpt.InsertCommand = sCommand; // Specify the batch size Size = Names.Count; adpt.UpdateBatchSize = size; // Open the connection objConnection.Open(); RowsAffected = adpt.Update(dt); objConnection.Close(); – Henry Feb 17 '11 at 07:01
2

@user619846: please **do not** post code and stuff into comments - it's very very hard to read!! If you have code, please **UPDATE** your original question by **EDITING** it and provide that code!! – marc_s Feb 17 '11 at 07:24

score 0 · Answer 2 · edited May 23 '17 at 10:24

Within the stored procedure, for the row to be added to the database, first check if the row is present in the table. If it is present, UPDATE it, otherwise INSERT it. SQL 2008 also has the MERGE command, which essentially moshes update and insert together.

Performance-wise, RBAR (row-by-agonizing-row) is pretty inefficient. If speed is an issue, you'd want to look into the various "insert a lot of rows all at once" procsses: BULK INSERT, the bcp utility, and SSIS packages. You still have the either/or issue, but at least it'd perform better.

Edit:

Bulk inserting data into an empty table is easy. Bulk inserting new data in a non-empty table is easy. Bulk inserting data into a table where some of the data (as, presumably, defined by the primary key) is already present is tricky. Alas, the specific steps get detailed quickly and are very dependent upon your system, code, data structures, etc. etc.

The general steps to follow are: - Create a temporary table - Load the data into the temporary table - Compare the contents of the temporary table with those of the target table - Where they match (old data), UPDATE - Where they don't match (new data), INSERT

I did a quick search on SO for other posts that covered this, and stumbled across something I'd never thought of. Try this; not only would it work, its elegant.

I agree with you but Im not all that good with SQL is it possible if you can give me an example of how I would go about checking the presence of the row? also perfomance is definately an issue so I will look into BULK INSERT — Henry, Feb 16 '11 at 15:01

score 0 · Answer 3 · answered Feb 16 '11 at 15:53

0

The easiest way would probably be to have an inner try block inside your loop. Catch any DB errors and re-throw them if they are not a duplicate record error. If it is a duplicate record error, then don't do anything (eat the exception).

answered Feb 16 '11 at 15:53

TMN

3,060
21
23

score -1 · Answer 4 · answered Feb 16 '11 at 14:47

-1

Does your table have a primary key? If so you should be able to check that the key value to be inserted is not already in the table.

answered Feb 16 '11 at 14:47

Colin

846
7
16

1

My table does have a primary key (I use IDENTITY) but I want to prevent the same name from being inserted that is to say a Column called ContactName if Peter jones exist then Peter Jones from the excel file should not be inserted again – Henry Feb 16 '11 at 14:55

check existing record before inserting

4 Answers4