tags:

views:

37

answers:

1

hi,

I have a Tariffs table for international dialing Codes
with StartDate and EndDate

I'm using ASP.net Application to import excel offers to this table , Each offer contain about 10000 row, so it is a large table (about 3 millions row) what is the faster scenario in SQL Server 2008 to create a stored-procedure or trigger to change the previous endDate for same tariff same prefix same destination and new rate on insert a new row, and how to undo saving offer of 10000 rows and get back the table and update records to the previous state

Thank you,

+1  A: 

The information in your question seems a bit jumbled, partially because of the ideas within it but also unhelpful grammer/whitespace (sorry to be so blunt but these things are helpful) but I'll try my best to answer.

In general, assume that a trigger is slower than a stored proc. They also add a higher level of complexity than many other things, like procs, so always be sure you really need one before using one.

But, I don't understand why you'd need a trigger if you're only inserting into one table. Triggers are usually used to implement a complex chain of logic. If it's a straight insert or update then keep simple and use a proc.

If it's just an insert, then the quickest way of all is a bulk insert.

Since you want to keep the previous state, my advice would be to create an archive/audit table (basically a duplicate, with possibly some extra fields like WhenInserted etc), on insert move (i.e. insert in the new table and then delete from the original) the existing rows into the archive and then you can do a bulk insert for the new rows.

But you use the word "change", so it's difficult to know what you really want. Hope that helps.

Iain