SQLTeam.com | Weblogs | Forums

Copying data from multiple tables and inserting new rows into them


I have a requirement for copying an entity (ex: Program) from web (ASP.NET MVC page) where user can select an option to copy entire data related to a Program. In this case Program entity has data in atleast 30 to 40 tables and currently we have a stored procedure that takes ProgramID and then uses bunch of MERGE USING WHEN NOT MATCHED THEN INSERT (to cater to few business rules and logic), AND INSERT INTO statements to destination tables. The entire SP is in a transaction block and since there are quite a few tables involved, this operation is locking DB and also we are performance issues. Also since the user is doing this action from screen, the expectation on the response time is somewhere under a minute

Any alternative suggestions on improving the performance?


Don't know if it would help, but you could pull all the required rows, from all the relevant tables, into #TempTables first, and then start the transaction and the INSERT into the relevant tables.

Your definition of the task sounds like no records should pre-exist, yet you are using MERGE so presumably some data will pre-exist? Might help to understand (in general terms) what data can pre-exist - as the act of "Copy" sounds, to me, like there should be no pre-existing data.