HOW TO INSERT MUTIPLE UNIQUE ROWS INTO A TABLE

marcel ethan

Total Post:104

Points:728
Posted by  marcel ethan
 1012  View(s)
Ratings:
Rate this:

I am not familiar with SQL that much. I'm trying to insert multiple rows of data into a table that if there exist a row with with duplicate value in BusinessFilterPhrase column then just don't insert. I wrote a pseudocode of what I think it should be.

if (filterCategoryList != null)
{
   foreach (KeyValuePair<string, int> filter in filterCategoryList)
   {
      cmd.CommandText = "insert into tblBusinessName (BusinessFilterPhrase,BusinessCategoryID)" +
                        "select @BusinessFilterPhrase,@BusinessCategoryID" +
                        "from tblBusinessName as t1" +
                        "where NOT EXISTS" +
                        "( select * from tblBusinessName as d1 where d1.BusinessFilterPhrase = @BusinessFilterPhrase) ";
 
      cmd.Parameters.AddWithValue("@BusinessFilterPhrase", filter.Key);
      cmd.Parameters.AddWithValue("@BusinessCategoryID", filter.Value.ToString());
 
      cmd.ExecuteNonQuery();
   }
}

 

  1. Takeshi Okada

    Post:89

    Points:629
    Re: How to Insert mutiple unique rows into a table

    You code looks correct. I would write it as:

    insert into tblBusinessName(BusinessFilterPhrase, BusinessCategoryID)
        select @BusinessFilterPhrase, @BusinessCategoryID
        from tblBusinessName t1
        where NOT EXISTS (select 1
                          from tblBusinessName d1
                          where d1.BusinessFilterPhrase = @BusinessFilterPhrase
                         )

    (The changes are only cosmetic.)

    EDIT:

    If performance is an issue, create an index on BusinessFilterPhrase:

    create index idx_tblBusinessName_BusinessFilterPhrase on tblBusinessName(BusinessFilterPhrase);

    You can make this a unique index, if you want the database to enforce the uniqueness of the column (it will generate an error when duplicate values would be inserted).

      Modified On Apr-07-2018 06:40:56 AM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!