Looping In Trigger?

I got this following scenario, There are four tables COUNTRY, STATE, CITY, STREET And I have the excel file with the records of the above..possibly 2000 rows as of now. I used SqlB

Solution 1:

Your first isssues is that you should never consider looping through a record set as a first choice. It is almost always the wrong choice as it is here. Your next problem is that triggers processs the whole set of records not one at a time and from your description, I'll bet you wrote it assuming it would process one record at a time. You need a set-based process.

Likely you need something like this in your trigger which would insert all countries in inserted that aren't already in the country table (this assumes country_Id is an integer identitiy column):

Insert country (country_name)
select country_name 
from inserted i
wherenotexists 
  (select*from country c 
   where c.country_name = i.country_name)

You also could use a stored proc instead of a trigger to insert into the real tables from the staging table.

Solution 2:

I would never put any such processing intensive task into a trigger on a table used for bulk load ! And never ever start putting loops like cursors and stuff like that into a trigger - a trigger must be small, lean and mean - just a quick INSERT into an audit table or something - but it should not do heavy lifting!

What you should do is this:

• use SqlBulkLoad to get your data into that staging table as quickly as possible, no triggers or anything
• then based on that staging table, do the necessary post-processing by splitting up column values and stuff like that

Otherwise, you're totally killing off any benefit that SqlBulkLoad has..

And to do this post processing (like determining Country_ID for a given Country), you don't need no cursors or any of those evil bits - just use standard, run-of-the-mill UPDATE statements on your table - that's all you need.