November 20, 2008 at 6:01 pm
wht is wrong in the below cursor ?
When execute it double rows get inserted ....
declare @name varchar(100)
DECLARE TestCursor CURSOR FOR
SELECT name from filename
OPEN TestCursor
FETCH NEXT FROM TestCursor INTO @name
WHILE @@FETCH_STATUS = 0
Begin
Insert into FieldTable (Name) values(@name)
FETCH NEXT FROM TestCursor into @name
End
CLOSE TestCursor
DEALLOCATE TestCursor
GO
November 20, 2008 at 6:08 pm
First, why are you using a cursor to begin with?
Second, you haven't provided enough information for use to even help you. Please read the article below in my signature block about asking for help. There is more we need, in a format directly usable (cut, paste, execute) in Management Studio.
November 20, 2008 at 8:55 pm
I want to do further processing on this table that is too by another cursor.
November 20, 2008 at 9:05 pm
First, WHY?
Second, set based solutions in SQL Server will work faster and scale a lot better than a cursor based solution.
Please read the article below about how to post questions for better responses to your problem. If you folow the guidelines presented, don't be surprised if someone finds you a set-based solution that blows the pants off your cursor based solution.
November 20, 2008 at 9:06 pm
bang725 (11/20/2008)
I want to do further processing on this table that is too by another cursor.
Not enough information. If you want help, please give us enough info to help you.
In the code sample you gave, the only way duplicate rows can get inserted is if there's duplicate rows in the source table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2008 at 9:30 pm
I have done some analysis on the issue and found that if there is a one row in the table,then it will insert double the rows in the table and goes on increasing the rows by double.
declare @name varchar(100)
DECLARE TestCursor CURSOR FOR
SELECT distinct username from test
OPEN TestCursor
FETCH NEXT FROM TestCursor INTO @name
WHILE @@FETCH_STATUS = 0
Begin
Insert into test (userName) values(@name)
FETCH NEXT FROM TestCursor into @name
End
CLOSE TestCursor
DEALLOCATE TestCursor
It worked for me and inserted only one row..
November 20, 2008 at 9:39 pm
First, by adding the DISTINCT to your select statement in your cursor definition, you eliminated all duplicates.
Second, this will not scale to very large tables, and there is most likely a simple set-based solution to your problem that you are simply avoiding.
Third, if you take the time to read the article I have linked in my signature block about how to ask questions; I'm sure given the proprer information you will have a set-based solution rather quickly.
November 20, 2008 at 9:50 pm
Nandy (11/20/2008)
I have done some analysis on the issue and found that if there is a one row in the table,then it will insert double the rows in the table and goes on increasing the rows by double.
Sure. You're inserting into the table that the cursor is based on, and it's a dynamic cursor. That means that changes to the table must be reflected in the cursor, hence duplicates. In your first example, the source of the cursor and the table ypu were inserting into were two different tables.
The distinct is fixing nothing. It's just hiding the problems.
What are you trying to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply