SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help capture error msg into another table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NguyenL71
Posting Yak Master

USA
208 Posts

Posted - 11/15/2013 :  12:11:23  Show Profile  Reply with Quote

Hi,

One of my co-worker told me I can do this to capture errors and insert into error table but when I
test it, it doesn't work. Here is what I try to accomplish. SQL 2012. In reality, I have more complicate queries than below.


1. Insert data FROM SourceEmployee INTO Employee table and capture emp_id and error msg insert into dbo.##temperror table
2. Continue on the process until no more record. Basically, skip the error records and do a while loop until end of record.

Any suggestions how to do these would greatly appreciate. Please see the output below.

Thank you so much in advance.



-------------------------------------------------------------------------------------

--DROP TABLE dbo.Employee;

CREATE TABLE [dbo].[Employee]
(
[emp_id] [int] NOT NULL,
[last_name] [varchar](20) NULL,
[first_name] [varchar](15) NOT NULL,
[salary] [money] NULL,
[email] [varchar](100) NOT NULL,
CONSTRAINT [XPKEmployee] PRIMARY KEY CLUSTERED ([emp_id] ASC )
) ON [PRIMARY]

GO

INSERT INTO dbo.Employee([emp_id],[last_name],[first_name],[salary],[email])
VALUES (1, 'Smith', 'John', 800.00, 'smithJohn@hotmail.com' ) ;

INSERT INTO dbo.Employee([emp_id],[last_name],[first_name],[salary],[email])
VALUES (2, 'Pham', 'Kristine', 200.00, 'PhamKristine@hotmail.com' ) ;

INSERT INTO dbo.Employee([emp_id],[last_name],[first_name],[salary],[email])
VALUES (3, 'Nguyen', 'Brandon', 1000.00, 'nguyenbrandon@hotmail.com' ) ;
GO


--DROP TABLE [dbo].[SourceEmployee];

CREATE TABLE [dbo].[SourceEmployee]
(
[emp_id] [int] NULL,
[first_name] [varchar](15) NULL,
[salary] [money] NULL,
[email] [varchar](50) NULL
) ON [PRIMARY]
GO

INSERT INTO dbo.SourceEmployee([emp_id],[first_name],[salary],[email])
VALUES (6, 'Peter', NULL, 'Peter@hotmail.com' ) ;

INSERT INTO dbo.SourceEmployee([emp_id],[first_name],[salary],[email])
VALUES (7, NULL, NULL, 'doe@hotmail.com' ) ;

INSERT INTO dbo.SourceEmployee([emp_id],[first_name],[salary],[email])
VALUES (8, NULL, 100.00, NULL ) ;
GO


DROP TABLE dbo.##temperror;
GO
CREATE TABLE dbo.##temperror
(
emp_id INT NULL,
errmsg VARCHAR(8000) NULL
)
GO

------------------------------------------------------------------------------------

--Testing...


DECLARE @TesVal BIT = 1
WHILE @TesVal = 1
BEGIN TRY

INSERT Employee (emp_id, last_name, first_name, salary, email)
SELECT emp_id, '', first_name, salary, email
FROM dbo.SourceEmployee

SET @TesVal = 0

END TRY

BEGIN CATCH


DECLARE
@ErrorNumber INT = ERROR_NUMBER(),
@ErrorSeverity INT = ERROR_SEVERITY(),
@ErrorState INT = ERROR_STATE(),
@ErrorProcedure NVARCHAR(126) = ERROR_PROCEDURE(),
@ErrorLine INT = ERROR_LINE(),
@ErrorMessage NVARCHAR(MAX) = REPLACE(ERROR_MESSAGE(), '''', '`')

SET @ErrorMessage = '<Error>'
+ '<ErrorNumber>' + ISNULL(CAST(@ErrorNumber AS NVARCHAR(100)), 'NULL') + '</ErrorNumber>'
--+ '<ErrorSeverity>' + ISNULL(CAST(@ErrorSeverity AS NVARCHAR(100)), 'NULL') + '</ErrorSeverity>'
--+ '<ErrorState>' + ISNULL(CAST(@ErrorState AS NVARCHAR(100)), 'NULL') + '</ErrorState>'
+ '<ErrorProcedure>' + ISNULL(@ErrorProcedure, 'NULL') + '</ErrorProcedure>'
+ '<ErrorLine>' + ISNULL(CAST(@ErrorLine AS NVARCHAR), 'NULL') + '</ErrorLine>'
+ '<ErrorMessage>' + ISNULL(@ErrorMessage, 'NULL') + '</ErrorMessage>'
+ '</Error>';

--PRINT @ErrorMessage
--INSERT ##temperror
--SELECT emp_id

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

--THROW
PRINT 'go here'

END CATCH;
go

--SELECT *
-- FROM dbo.Employee;
--GO

----------------------------------------------------------------------------------------------------------



-- Results want:
SELECT * FROM dbo.##temperror;


emp_id errmsg
------ -------
7 Msg 50000, Level 16, State 2, Line 46
<Error><ErrorNumber>515</ErrorNumber><ErrorProcedure>NULL</ErrorProcedure><ErrorLine>16</ErrorLine><ErrorMessage>Cannot insert the value NULL into column `first_name`, table `CDCTest.dbo.Employee`; column does not allow nulls. INSERT fails.</ErrorMessage></Error>

8 Msg 50000, Level 16, State 2, Line 46
<Error><ErrorNumber>515</ErrorNumber><ErrorProcedure>NULL</ErrorProcedure><ErrorLine>16</ErrorLine><ErrorMessage>Cannot insert the value NULL into column `first_name`, table `CDCTest.dbo.Employee`; column does not allow nulls. INSERT fails.</ErrorMessage></Error>


1 row insert to Employee table successfully. Should be emp_id = 6

--SELECT *
-- FROM dbo.Employee;
--GO


(6, 'Peter', NULL, 'Peter@hotmail.com' )

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/15/2013 :  12:40:44  Show Profile  Reply with Quote
Here is one way:
DECLARE @EmpID INT;

SET @EmpID = (SELECT MIN(emp_id) FROM dbo.SourceEmployee);


WHILE @EmpID IS NOT NULL
BEGIN

	BEGIN TRY
		INSERT 
			dbo.Employee
			(
				[emp_id],
				[first_name],
				[salary],
				[email]				
			)
		SELECT
			[emp_id],
			[first_name],
			[salary],
			[email]			
		FROM
			dbo.SourceEmployee
		WHERE
			emp_id = @EmpID;


	END TRY
	BEGIN CATCH

		INSERT
			##temperror
			(
				emp_id,
				errmsg
			)
		VALUES
			(
				@EmpID,
				ERROR_MESSAGE()
			)

	END CATCH

	SET @EmpID = 
		(
			SELECT 
				MIN(emp_id) 
			FROM 
				dbo.SourceEmployee
			WHERE
				emp_id > @EmpID
		);

END


SELECT *
FROM ##temperror

SELECT *
FROM dbo.Employee
Go to Top of Page

NguyenL71
Posting Yak Master

USA
208 Posts

Posted - 11/15/2013 :  13:07:47  Show Profile  Reply with Quote
Thank you so much for taking the time to help. But the real queries, it's not one SourceEmployee table.

Thank you and have a great Weekend.

Here is the queries and I can't think how to capture all the errors in
the select columns.
WITH CTE1
AS
(SELECT
acct.accountId
,apl.AccountProductLine_ID
,pls.AccountProductLineStatusType_ID
,ROW_NUMBER() OVER (PARTITION BY acct.Entity_ID,apl.AccountProductLine_ID -- put NULL at bottom of list when it exists
ORDER BY pls.AccountProductLineStatusType_ID DESC, pls.EffectiveDate DESC, pls.AccountProductLineStatus_ID DESC) RowNumber
FROM Membership.Account acct
INNER JOIN Membership.AccountContract ac ON acct.Entity_ID = ac.Account_ID
AND ac.RenewalDate >= GETUTCDATE() and ac.IsDeleted=0
--AND ac.EffectiveDate <= GETUTCDATE()
--AND ac.EnrollmentDate <= GETUTCDATE() /* is this necessarY? */
INNER JOIN Membership.AccountProductLine apl ON ac.AccountContract_ID = apl.AccountContract_ID
-- AND ap.EffectiveDate <= GETUTCDATE()
-- AND ap.ApprovalDate <= GETUTCDATE() /* ??? */
INNER JOIN Membership.AccountProductLineStatus pls ON apl.AccountProductLine_ID = pls.AccountProductLine_ID
AND pls.EffectiveDate <= GETDATE()
AND pls.AccountProductLineStatusType_ID IN (2 /*ApproveContingent*/, 4/*InForce*/, 6/*Reinstated*/)
),
CTE2
AS
-- Now we are only looking at the active product lines as of today. Next check the Enrollment status as of today by getting the
-- status of the most recent effective date before today for each product and see if any of them active.
(SELECT
cte1.accountid
,cte1.AccountProductLineStatusType_ID
,cte1.AccountProductLine_ID
, ee.ProductLineType_ID
,es.EffectiveDate
,es.EnrollmentStatus_ID
, es.EnrollmentStatusType_ID
,ROW_NUMBER() OVER (PARTITION BY cte1.Entity_ID -- put NULL at bottom of list when it exists
ORDER BY es.EnrollmentStatusType_ID DESC, es.EffectiveDate DESC, es.EnrollmentStatus_ID DESC) RowNumber
FROM cte1
INNER JOIN Membership.AccountProductLinePlan app
ON cte1.AccountProductLine_ID = app.AccountProductLine_ID
-- AND app.EffectiveDate >= GETUTCDATE()
INNER JOIN Membership.Enrollment enr ON app.AccountProductLinePlan_ID = enr.AccountProductLinePlan_ID
-- AND enr.EffectiveDate >= GETUTCDATE()
--AND enr.EnrollmentDate >= GETUTCDATE() /* ??? */
INNER JOIN Membership.EnrollmentElection ee ON enr.EnrollmentElection_ID = ee.EnrollmentElection_ID
INNER JOIN Membership.EnrollmentStatus es ON ee.EnrollmentElection_ID = es.EnrollmentElection_ID
AND es.EnrollmentStatusType_ID IN (1 /*Pending*/, 3 /*Enrolled*/)
AND es.EffectiveDate <= GETDATE()
WHERE cte1.RowNumber=1
)
--SELECT * FROM CTE2
INSERT INTO [etlUMB].[UMBAccountMasterDetailTransform]
(
UMBAccountMasterHeader_ID,
RecordType,
AccountHolderFirstName,
AccountHolderMiddleInitial,
AccountHolderLastName,
AccountHolderSSN,
EmailAddress,
AccountHoldersAddressLine1,
AccountHoldersAddressLine2,
AccountHolderCity,
AccountHolderState,
AccountHolderCountry,
AccountHolderZipCode1,
AccountHolderZipCode2,
CustomerAccountNumber,
CustomerAccountType,
CustomerAccountSubType,
AccountIssuerStateCode,
CurrentBalance,
CreditLimit,
MinimumPaymentDue,
PastAmountDue,
CurrentStatementBalance,
PaymentDueDate,
InvoiceNumber,
InvoiceDate,
EligibilityIndicator,
PartnerKey,
CustomerID,
CustomerOnlineEnrollmentCode,
Filler,
Account_ID,
AccountHolderEntity_ID,
HIXGroupID,
IsCurrent,
UpdateDate,
SecurityUser_ID,
RootKey,
HashKey,
SourceSystemID
)
-- Try to catch any errors before inserting into a table.
SELECT
col1,
col2,
col3,
col4,
....
SourceSystemId

FROM Membership.Account acct
INNER JOIN dbo.Entity e
ON acct.accountid = e.account
AND e.EntityType_ID = 1 -- Group
INNER JOIN Membership.AccountContract ac ON acct.Entity_ID = ac.Account_ID
AND ac.RenewalDate >= GETUTCDATE() and ac.IsDeleted=0
LEFT JOIN cte2 ON acct.Entity_ID = cte2.Entity_ID AND cte2.RowNumber=1
INNER JOIN dbo.Organization o
ON acct.Entity_ID = o.Entity_ID
AND o.OrganizationType_ID = 1 -- Group
--LEFT JOIN dbo.Organization_To_CommonValue O2C
-- ON O2C.Organization_ID = o.Entity_ID
-- JOIN dbo.CommonValue CV -- FIX change to INNER JOIN in Production
-- ON CV.CommonValue_ID = O2C.CommonValue_ID
-- AND CV.Name = 'EIN'
LEFT OUTER JOIN [etlUMB].[UMBAccountMasterDetailMap] M
ON e.HIXID = M.SourceID
AND M.SourceSystemID = 'HIX'
WHERE e.MYHID NOT IN ( SELECT MYHID
FROM @TempGroup ) -- Exclude the dup val.
--LEFT OUTER JOIN Membership.EnrollmentStatus es
-- ON es.SourceSystem_ID=e.SourceSystem_ID;


---------------------------------------------------------


quote:
Originally posted by Lamprey

Here is one way:
DECLARE @EmpID INT;

SET @EmpID = (SELECT MIN(emp_id) FROM dbo.SourceEmployee);


WHILE @EmpID IS NOT NULL
BEGIN

	BEGIN TRY
		INSERT 
			dbo.Employee
			(
				[emp_id],
				[first_name],
				[salary],
				[email]				
			)
		SELECT
			[emp_id],
			[first_name],
			[salary],
			[email]			
		FROM
			dbo.SourceEmployee
		WHERE
			emp_id = @EmpID;


	END TRY
	BEGIN CATCH

		INSERT
			##temperror
			(
				emp_id,
				errmsg
			)
		VALUES
			(
				@EmpID,
				ERROR_MESSAGE()
			)

	END CATCH

	SET @EmpID = 
		(
			SELECT 
				MIN(emp_id) 
			FROM 
				dbo.SourceEmployee
			WHERE
				emp_id > @EmpID
		);

END


SELECT *
FROM ##temperror

SELECT *
FROM dbo.Employee


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000