Transfering and verifying data using temp tables

Hi, I need to write code that uses temp tables to check data and transfer it to another table. As I understand, data has to be transfered to temp table, and then a loop will check each row for data integrity and move the valid rows into a new table. The code looks something like this. The problem is with this code, the destination table (table 3) contains only one record instead of 375. What am I doing wrong

Select CustomerID, StaffID, Sequence, Bldg, Floor
into #temptable
from [06w_manukyan]..[Table1]

declare @CustomerID char(10),
@StaffID char(6),
@Sequence int,
@Bldg char(3),
@Floor char(2)

set rowcount 1
Select @CustomerID=CustomerID, @StaffID=StaffID, @Sequence=Sequence, @Bldg=Bldg, @Floor=Floor
from #temptable
While @@rowcount = 1
--data integrity check goes here
Begin

--insert checked data into destination table
Insert into [06w_manukyan]..[Table3]
( CustomerID, StaffID, Sequence, Bldg, Floor)
values
(@CustomerID, @StaffID, @Sequence, @Bldg, @Floor)
delete from #temptable
Select @CustomerID=CustomerID, @StaffID=StaffID, @Sequence=Sequence, @Bldg=Bldg, @Floor=Floor
from #temptable
End

select * from [06w_manukyan]..[Table3]

Answer this question

Transfering and verifying data using temp tables

Nikolaj Berg Amondsen
Why can't you perform the data integrity checks as part of a single SELECT statement You can then simplify this entire code to series of INSERT, UPDATE, and DELETE statements. Alternatively, if the integrity checks are complex you can perform the data cleaning outside of the database using DTS or SSIS package. Doing row-by-row processing using cursors or loops is terribly inefficient and there are very few problems that require use of such processing.
pfpgroup

It seems you missed out to add where criteria in delete statement.

delete from #temptable Where CustomerID=@CustomerID AND StaffID=@StaffID AND [Sequence]=@Sequence AND Bldg=@Bldg AND [Floor]=@Floor
luis figueredo
Be sure to set the rowcount back to 0 after setting it to 1. Otherwise, all subsequent queries will only work with 1 record. This could be why your insert is only getting 1 result.

Is it really necessary to perform the validity checks on a row, by row basis Can the checks be turned into a series of set based queries that check different aspects of the rows

It is kind of odd to use simulated cursors, these days, when doing row at a time processing. If sets cannot be used, then use the cursors.

SSEGARANE

brokenrulz wrote:

I guess the problem is with the delete statement. you need to keep it outside the loop.

I still doubt you will get the correct records. I mean I am not sure how do you move to the next record after you insert the first.

Also ROWCOUNT is going to get deprecated.

Nope, if I move delete out of loop, the loop itself never ends. The rowcount should be depreciated, that's how the loop was supposed to work, i.e it would loop as long as there is one row left in the original table.

marlin1

hi there,

You can use the identity with your select into temptable

like this

use northwind
select counter = IDENTITY(int, 1, 1),lastname,firstname
into #tempx
from employees

and the do the cursor less processing like this

declare @ctr int

select @ctr =max(counter) from tempx

declare @acl int

select @acl = 0

while not @acl=@ctr

begin

--do your stuffs here

select @acl=@acl+1

end
Vegethalia
I would suggest, using a cursor with @@fetch_status. The sample code is :

DECLARE @MyInsertCursor CURSOR

-- Cursor Definition

SET @MyInsertCursor = CURSOR FAST_FORWARD
FOR
SELECT CustomerID, StaffID, Sequence, Bldg, Floor
from #temptable

OPEN @MyInsertCursor
FETCH NEXT FROM @MyInsertCursor INTO @CustomerID, @StaffID, @Sequence, @Bldg, @Floor

--LOOP all records from the inserted table

WHILE @@FETCH_STATUS = 0
Begin

--insert checked data into destination table
Insert into [06w_manukyan]..[Table3]
( CustomerID, StaffID, Sequence, Bldg, Floor)
values
(@CustomerID, @StaffID, @Sequence, @Bldg, @Floor)

FETCH NEXT FROM @MyInsertCursor INTO @CustomerID, @StaffID, @Sequence, @Bldg, @Floor
END

CLOSE @MyInsertCursor
DEALLOCATE @MyInsertCursor
n y n--d8bn pr95y6
Thanx, that would work except I am not supposed to use cursors (it's for a damn project). And I can't figure out why it won't loop the way it's supposed to.
maxcredible
I guess the problem is with the delete statement. you need to keep it outside the loop.

I still doubt you will get the correct records. I mean I am not sure how do you move to the next record after you insert the first.

Also ROWCOUNT is going to get deprecated.
Euclidez
I realize that' update insert and delete series is the easiest way but it's a school project and the prof requires using temp tables and no cursors. Efficiency is not the issue here, he just wants to make sure we can use those tables. I have tried everything, the loop will not go further than 1 row.
Transfering and verifying data using temp tables

Answer this question