Home > DeveloperSection > Forums > How to Insert mutiple unique rows into a table
marcel ethan
marcel ethan

Total Post:105

Points:735
Posted on    January-07-2015 10:23 PM

 ASP.Net C#  SQL Server 
Ratings:


 1 Reply(s)
 523  View(s)
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();

   }

}

 



Takeshi Okada
Takeshi Okada

Total Post:89

Points:629
Posted on    January-08-2015 2:51 AM

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


Don't want to miss updates? Please click the below button!

Follow MindStick