I have an application through which I'm inserting bulk records into a table using SqlBulkCopy. I have a trigger which inserts the records added into the First table into Another table.
When I'm inserting i have written SqlBulkCopyOptions.FireTriggers in C#, but its not working with Sql Server 2005. When worked with SqlServer2008 its working fine.
In Sql server 2005, Whenever the records are inserted into the First Table only one record from the INSERTED rows appear in the second table.
Can I manage this using Sql Server alone?
One option here would be to bulk-copy into a staging table (a second, simple table - not part of the main data model; usually no indexing / constraints / triggers / etc), and then issue a
INSERT to get the data from the staging table into the main transactional table. This allows you to get the bandwidth advantages of
SqlBulkCopy, but still have full SQL-Server functionality for the final