urgent please
-
i have a flatfile with data which comes everyday. and sometimes the data in the flat
file has some errors and i need to write a code which checks whether the file has errors
or not. if it has errors then all the errors should be written to a temp table.
example:
rec.code date time int_id
23 20050921 122432 ABC233
23 20050828 131411 ABC565
23 20050326 122526 ABC333
23 20050413 243453 ABC545
Rec.code should be of size 2.its should be same. and if the date field does not have
this format i.e if it has any characters it should generate an error. and the same with
time field. and the int_id should have the first 3 characters the same. if it is not 'abc'
it should generate an error.
we should check each column and each row. i think i have to use cursor for this but
not sure how to use it. will be thankful if anyone can help me out
-
It looks as if int_id is a Primary Key. You could that with a WHILE loop and drop the need for a cursor. Otherwise, you could make a Identity Field in your #TempTable and also drop the need for a cursor. There is an awful lot of overhead to a cursor and they should be used very sparingly.
I would suggest you start looking at BOL [Books On-Line] for flow control. For instance,
IF ISDATE( date) = 1
BEGIN
some action
END
ELSE
BEGIN
some other action
END
I wasn't born stupid - I had to study.
-
I would suggest that if the number of records is large you should not use cursors, use queries instead to search by column conditions
For example
you can insert in the error table all rows from this query
select * from
StagingTable
Where
Len([Rec.code]) > 2 --Check Length
Or
isdate([date]) = 0 -- Check valid Date
Or
isdate ('1900-01-01 ' + left([time],2) + ':' + substring(time,3,2) + ':' + Right(time,2) ) = 0
Or
int_id not Like 'ABC%'
once you have inserted those onthe temp just delete them from the staging table. Then you would write another query to aggregate the values across rows to check for multiple different values and repeat the process
hth
* Noel -
Or you could delete them right away from your staging table using the same where condition, but using a delete trigger to do the insert on the error table... That way you only have to scan the data once.
-
OR if you follow the Trigger idea from Remi the use an INSTEAD OF insert trigger to insert the good rows on the detination table and the bad ones on the Error table and no delete will be necessary
* Noel -
That's why 2 heads are always better than one
...I'm not sure which solution would be better because they all seem pretty much the same but in different order...
-
Thanks a lot for ur help. these answers helped me a lot. please give me some more suggestions if any one can.
-
You don't need more... you just need to code now
. -
Unless performance becomes a major problem, I'd go with a script instead of a trigger - I think its more managable.
Using Noel's suggestion, something along these lines might do it:
-- Save the rows that contain errors
INSERT ErrorTable
SELECT *
FROM StagingTable
WHERE Len([Rec.code]) > 2 --Check Length
OR IsDate([date]) = 0 -- Check valid Date
OR IsDate ('1900-01-01 ' + Left([time],2) + ':' + Substring([time],3,2) + ':' + Right([time],2) ) = 0
OR int_id NOT LIKE 'ABC%'
-- Delete the rows that contain errors from the staging table
DELETE StagingTable
WHERE Len([Rec.code]) > 2 --Check Length
OR IsDate([date]) = 0 -- Check valid Date
OR IsDate ('1900-01-01 ' + Left([time],2) + ':' + Substring([time],3,2) + ':' + Right([time],2) ) = 0
OR int_id NOT LIKE 'ABC%'
Also, if you MUST process the data row by row, and you have a primary key, you can use a WHILE loop instead of a cursor. Here's an example:
--DROP TABLE StagingTable
GO
CREATE TABLE StagingTable
(
int_id varchar(6) PRIMARY KEY
, stVal varchar(20) NOT NULL
)
SET NOCOUNT ON
INSERT StagingTable VALUES ('ABC123', 'Value for 123')
INSERT StagingTable VALUES ('ABC234', 'Value for 234')
INSERT StagingTable VALUES ('ABC345', 'Value for 345')
INSERT StagingTable VALUES ('ABC456', 'Value for 456')
INSERT StagingTable VALUES ('ABC567', 'Value for 567')
SET NOCOUNT OFF
DECLARE @int_id varchar(6) -- or whatever size is needed
, @stVal varchar(20)
, @rowCount int
SELECT @int_id = Min(int_id) FROM StagingTable
SET @rowCount = 0
WHILE @int_id IS NOT NULL
BEGIN
SET @rowCount = @rowCount + 1
SELECT @stVal = stVal FROM StagingTable WHERE int_id = @int_id
PRINT @int_id + ' : ' + @stVal
-- Get the next row
SELECT @int_id = Min(int_id) FROM StagingTable WHERE int_id > @int_id
END
PRINT 'Processed ' + CONVERT(varchar(10), @rowCount) + ' row(s).'
-
We were simply suggesting the trigger solution because it would avoid doing this complexe condition twice in a row :
WHERE Len([Rec.code]) > 2 --Check Length
OR IsDate([date]) = 0 -- Check valid Date
OR IsDate ('1900-01-01 ' + Left([time],2) + ':' + Substring([time],3,2) + ':' + Right([time],2) ) = 0
OR int_id NOT LIKE 'ABC%'
-
thanks a lot for ur replies. this code helped me a lot.
have a simple question. i have an address field in my table and if any time
the address of the person changes i need to know that the address of the particular
person has changed and i have to update it in the database.
-
You'll have to put a trigger on that table, and when the field(s) of the adress are changed, you'll have to insert the old data in another table to keep an audit trail.
-
hi.........
i need to select count from one table and compare with the count in other table.
i mean
if select count (*) from table1= select count(*) from table2
need to continue with in next operation. can u help me how to query this.
will be thankful if anyone can help me out
-
Not sure of what you really want to do here but this could work :
if (Select count(*) from dbo.SysColumns) = (Select count(*) from dbo.SysObjects)
begin
--do your thing
end
else
begin
--do your thing
end
-
thank u this is what i want. igot it
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply