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 SqlBulkCopy to import the data to a temp table, lets name the table IMPORT.
And i wrote one trigger for insert on the IMPORT table which gets the inserted record
and splits country, state, city, street then inserts them to the respective table.
In this trigger i got to do some conditional check,like, if COUNTRY name is already present then returns the COUNTRY_ID else insert it and get the new COUNTRY_ID.
The above works if the Excel file has only one row.
Once i put the original Excel for the import i figured it out the following statement in the trigger fails "select country from INSERTED" because sqlbulkcopy makes INSERTED to has more than one records.
Table Structure
COUNTRY
STATE
- State_ID
- Country_ID
- State_Name
CITY
- City_ID
- State_ID
- Country_ID
- City_Name
STREET
- Street_ID
- City_ID
- State_ID
- Country_ID
- Street_Name
IMPORT
- Country_Name
- State_Name
- City_Name
- Street_Name
So can i have loop statement in trigger that will loop through all the records in INSERTED?
Or how to address this in the best way?
NOTE: Since they are already using it, i've got no control over those table structure and their relationships.
Thanks in advance.
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
where not exists
(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.
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.
