April 3, 2012 at 8:39 am
Hi,
Please give any ideas on implementing error output using T-SQL.
I am trying to import data from table A to table B using INSERT INTO SELECT. During import, some records might file due to errors. I won't know all the possible errors before hand. I need to be able to insert all the *good* records into the destination and capture bad records (error'ed rows to another table).
I am not using SSIS, so using it's error output feature isn't an option. Can this be possible without a cursor?
Thanks.
April 3, 2012 at 8:52 am
Are you familiar with a TRY CATCH block if not start here:
http://msdn.microsoft.com/en-us/library/ms175976(v=sql.100).aspx
Also please note if you post table definition(s), sample input and desired output you are more likely to get a tested answer. To do this please click on the first link in my signature block
April 3, 2012 at 9:00 am
It's possible to do what you're asking in T-SQL ETL without loop (cursor), but you need to implement all validations!
Basically the idea:
Apply validation rules for all rows in your "source" table and populate "errors" table with any errors found (you can insert Id's and error descriptions there).
Then insert into "destination" all rows which didn't failed validation (not found in "errors" table.
April 3, 2012 at 9:25 am
An expansion on my prior post.
CREATE PROCEDURE [dbo].[usp_GetErrorInfo]
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
Then using the above:
CREATE TABLE #E(Enr INT,ES INT,Estate INT,Eline INT,EProc VARCHAR(100),EMessage VARCHAR(100))
--To illustrate
BEGIN TRY
SELECT 1/0; -- Generate divide-by-zero error.
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
INSERT INTO #E
EXECUTE usp_GetErrorInfo;
END CATCH;
SELECT * FROM #E
Result:
8134161NULL2Divide by zero error encountered.
Hope this helps you develope what you require.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply