CODEWITHSUNDEEP
sql-serverc#-2.0
Henry
Henry

Reputation: 41

check existing record before inserting

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?

Upvotes: 2

Views: 6512

Answers (4)

Philip Kelley
Philip Kelley

Reputation: 40309

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.

Upvotes: 0

TMN
TMN

Reputation: 3070

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).

Upvotes: 0

marc_s
marc_s

Reputation: 754368

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)

Upvotes: 4

Colin
Colin

Reputation: 855

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.

Upvotes: -1

Related Questions