urgent please
-
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
-
we will be getting data in flatfiles and before transferring it into server i need to check
if the flatfile has errors in it. i mean we need to check each and every position and if
it has error it should be transferred into another file.
01XYZ234 27838378278 12 20050912
the position of first field is 1-2
second field is 3-13.......so on
seeing the positions i need to test that if the first field has anything other than 01
its an error. similarly it should check for each and every field.
i need to write a SP for this. will be great if anyone can help me out with this problem. i
am confused of what to do
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply