How Can I replace a cursor in sql server 2008

In the last years I have put lot of effort in c# and left sql server a bit . My sql skills could be better. I know cursors are slow etc... I have put together a noddy example that I seem to encounter quite a bit at work.

I need to migrate data from one flat table "Customer"

into many tables

"CustomerAddress" "CustomerPhone" etc..

If you were assigned this task how would you do it without using cursors?

Cursor to convert

            BEGIN TRANSACTION


            DECLARE @CustomerID int,
                    @Name nvarchar(50),
                    @Surname nvarchar(50),
                    @DateOfBirth datetime,
                    @Address nvarchar(200),
                    @City nvarchar(50),
                    @County nvarchar(50),
                    @Country nvarchar(50),
                    @HomePhone nvarchar(20)


            DECLARE OldCustomerCursor CURSOR FAST_FORWARD
            FOR
            SELECT CustomerID,Name,Surname,DateOfBirth,Address,City,County,Country,HomePhone
            FROM OldCustomer

            OPEN OldCustomerCursor

            FETCH NEXT FROM OldCustomerCursor INTO  @CustomerID,
                                                    @Name ,
                                                    @Surname ,
                                                    @DateOfBirth ,
                                                    @Address ,
                                                    @City ,
                                                    @County ,
                                                    @Country ,
                                                    @HomePhone

            WHILE @@FETCH_STATUS = 0
            BEGIN


                INSERT [dbo].[Customer] ([CustomerID], [Name], [Surname], [DateOfBirth])
                VALUES(@CustomerID,@Name,@Surname,@DateOfBirth)  

                INSERT [CustomerAddress]([AddressID],[CustomerID],[Country],[Address],[City],[County])
                VALUES(@Count,@CustomerID,@County,@Address,@City,@Country)

                INSERT [dbo].[CustomerTelephone]([TelephoneID],[CustomerID],[Number])
                VALUES(@Count,@CustomerID, @HomePhone)

              FETCH NEXT FROM OldCustomerCursor INTO @CustomerID,
                                                 @Name ,
                                                 @Surname ,
                                                 @DateOfBirth ,
                                                 @Address ,
                                                 @City ,
                                                 @County ,
                                          开发者_开发问答       @Country ,
                                                 @HomePhone
            END

            CLOSE OldCustomerCursor
            DEALLOCATE OldCustomerCursor

            SELECT  * FROM    Customer
            SELECT  * FROM    CustomerAddress
            SELECT  * FROM    CustomerTelephone
            ROLLBACK TRANSACTION

Thanks for any suggestions how to replace a cursor

---

INSERT INTO tablename (column1, column2 ...)
SELECT column1, column2...
FROM mastertable

Do it for each group of columns and table.

---

I don't see any reason to use cursors you can try it like this

SELECT CustomerID,Name,Surname,DateOfBirth,Address,City,County,Country,HomePhone
FROM OldCustomer

INSERT [dbo].[Customer] ([CustomerID], [Name], [Surname], [DateOfBirth])
SELCT CustomerID,Name,Surname,DateOfBirth
FROM  OldCustomer

INSERT [CustomerAddress]([AddressID],[CustomerID],[Country],[Address],[City],[County])
SELECT Count,CustomerID,County,Address,City,Country 
FROM  OldCustomer

INSERT [dbo].[CustomerTelephone]([TelephoneID],[CustomerID],[Number])
SELECT Count,CustomerID, HomePhone
FROM OldCustomer

---

Suggest you read this for ideas on how to avoid cursors. http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

Also look at the OUTPUT clause to get your GUIDs (and natural keys so you know which record to associate them with) back out if they are being generated by the system.

---

I had a stored procedure that was doing claims adjudication: looking at each claim via cursor and then processing it by populating the necessary tables based on the type of claim. It was horribly slow - took hours to run. I optimized it by processing each group of similar claims using set-based SQL. It ran in seconds.

Usually, people think they need cursors to represent a series of conditions:

DECLARE UserCursor CURSOR
FOR SELECT UserType, UserID FROM Users
OPEN UserCursor
FETCH NEXT whatever
CASE UserType 
  WHEN 'Employee' THEN do something
  WHEN 'Manager' THEN do another thing
  WHEN 'Owner' THEN do get coffee
  WHEN 'Customer' THEN take money
END
CLOSE CURSOR

or something like that.

In reality, you can do this:

SELECT UserType, UserID
FROM Users
WHERE UserType = 'Employee'
  do something

SELECT UserType, UserID
FROM Users
WHERE UserType = 'Manager'
  do another thing

etc

This looks worse to a procedural programmer person, but it is so much faster it isn't even funny. Don't think about row-based processing, think about set-based processing. That's what databases are made for.

---

You can use table variables instead of cursors

--declare table variable 
declare @tblCustomersVar table
(
    CustomerID int,
    Name nvarchar(50),
    Surname nvarchar(50),
    DateOfBirth datetime,
    Address nvarchar(200),
    City nvarchar(50),
    County nvarchar(50),
    Country nvarchar(50),
    HomePhone nvarchar(20)
)
insert into @tblCustomersVar
 SELECT CustomerID,Name,Surname,DateOfBirth,Address,City,County,Country,HomePhone
 FROM OldCustomer

 --declare @counter variable
 declare @counter int
 declare @rowCount int
 set @counter=1
 set @rowCount=(select COUNT(*) from @tblCustomersVar)

 while(@counter<=@rowCount)
  Begin
    --process here

  --increment
     set @counter=@counter+1
  End