|
Category: SQL Server
view more software Tips and Tricks
Updated: 09/29/2013 10:09 AM
Author: Shiju Mathews Status: Resolved. |
|
Most programmers are replacing "Sql cursor" with while loops for performance. In some cases it also is possible to boost the stored procedure / T SQL performance by replacing the even the while loops with set based solutions. Download the example file Consider the following reference Tables tblEmployee and tblEmployeePhone. Here [dbo].[tblEmployeePhone] foreign keyed to [dbo].[tblEmployee] and there is a one to many relatopn between tblEmployee and tblEmployeePhone. The company management decided to Open a new Plant at ‘Jacksonville’ and requset all of the employes to move to the new plant. But management also decided to create new employee number to the employes at the new plant, while keeping the old records the same. So the progmamer need to create new employeeId for each tranfered employee and copy their data from the tblEmployeePhone with new employeeID. The traditional way is to use a cursor, inert each employee the data and Grab the new EmployeeID and using the SCOPE_IDENTITY()and continue with the insert. When there is more nested tables and large amount of data are involved this will sucks in performance. Consider an alternate method. Insert Existing employees to the table to create new EmployeeID. Create a table variable for new and old EmployeeID Fill the table with old and new EmployeeID Use the values of oldEmployeeId and new EmployeeID from @EmployeeCon for inserting to tblEmployeePhone Download the example file |