October 20, 2016 at 7:52 pm
If error<>0 I want to track or capture one column in error table and if error = 0 the no of rec insertion increments and loads that var in to log table..how to add that logic for below proc.
SET NOCOUNT ON
DECLARE @NoRecInserted INT,
@Error INT
SET @NoRecsInserted = 0
SELECT
col1,
col2
into #tmpFinal
FROM tmp.Employee
WHERE RD NOT IN
(SELECT ID FROM dbo.Employee WHERE EMP-CODE <> -1)
INSERT INTO dbo.Employee
(
col1,col2
)
Select
col1,
col2,
T1.Stdname
FROM dbo.Student T1
JOIN #tmpStageFinal tmp
on tmp.ID = t1.ID
DROP TABLE #tmpFinal
October 21, 2016 at 8:20 am
Here is an article on Handling Errors in SQL Server 2012[/url]. It's not clear whether the errors you mention are being produced earlier in the procedure or as part of the insert you have listed, so it's difficult to give you any more detailed information about how to set this up.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 21, 2016 at 9:26 am
Let me clarify here - The code which I mentioned I am not using any cursor its just a bulk insert..
In the old procedure the people who coded was using cursors see below plz..In my code which I mentioned earlier I am not using cursor I don't see any reason why they are doing it. Now I got stuck how to implement error and no of rec inserted in my procedure which I created with out cursor..We can use rowcount I guess but I want to go in this way.
DECLARE
@col1 varchar(10) NULL
@NoOfRecsInserted INT,
@Error INT
SET @NoOfRecsInserted = 0
DECLARE LCursor CURSOR STATIC FOR
SELECT
col1
FROM temp.employee WHERE ID NOT IN
(SELECT ID FROM dbo.Employee WHERE EmpCode <> -1)
OPEN LCursor
FETCH NEXT FROM LCursor INTO
@col1
WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.Employee
(
col1
)
VALUES
(
@col1
)
SET @Error = @@Error
IF @Error <> 0
BEGIN
SET NOCOUNT ON
SELECT 'col1' + CAST(@col1 AS VARCHAR(50))
SET NOCOUNT ON
INSERT INTO dbo.Error
(Name,Pronameame,KeyValue,Error)
VALUES
('InsEmp','Insertemployees','@col1',@Error)
END
IF @Error = 0
BEGIN
SET @NoOfRecsInserted = @NoOfRecsInserted + 1
END
FETCH NEXT FROM LCursor INTO
@col1
END
CLOSE LCursor
DEALLOCATE LCursor
EXEC dbo.Log 'tablename',@NoOfRecsInserted,0,0,'InsertEmployees'
October 21, 2016 at 12:26 pm
First off, switch to using BEGIN TRY ... END CATCH rather than just the error code. That will give you a lot more options and capabilities on your error handling.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 21, 2016 at 2:41 pm
When switching from row-based processing to set-based processing, you have to understand that the set will either succeed or fail as a whole. You can either pre-screen your data to catch potential errors and only insert rows that are valid or you can use something like SSIS which processes the rows individually and can give you row-level error handling.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply