HOW TO INSERT MUTIPLE UNIQUE ROWS INTO A TABLE

marcel ethan

Total Post:105

Points:735
Posted by  marcel ethan
 819  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).

Answer

NEWSLETTER

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