March 13, 2013 at 9:12 am
Hello -
I created a Store Procedure to have Table A sync with Table B by doing an insert of the data that it does not have.
The issue I'm running into is when I run the Stored Proc it's taking longer then just running the Insert itself. I want to put this into a Job to run at night but also to have it in a Transaction in case I need to rollback as well on an error. Can someone take a look to see if I have to much or not enough in my script?
USE [Dev_SageReporting]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[hgsp_backloglaborb_sync]
AS
DECLARE @intErrorCode INT
BEGIN TRAN
INSERT INTO BackLogLaborB
SELECT *
FROM BackLogLaborCurrent AS A
WHERE NOT EXISTS ( SELECT *
FROM BackLogLaborB AS B
WHERE A.PIT = B.PIT )
SET NOCOUNT ON;
SELECT @intErrorCode = @@ERROR
IF ( @intErrorCode <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Regards,
David
March 13, 2013 at 9:20 am
david.ostrander (3/13/2013)
Hello -I created a Store Procedure to have Table A sync with Table B by doing an insert of the data that it does not have.
The issue I'm running into is when I run the Stored Proc it's taking longer then just running the Insert itself. I want to put this into a Job to run at night but also to have it in a Transaction in case I need to rollback as well on an error. Can someone take a look to see if I have to much or not enough in my script?
USE [Dev_SageReporting]GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[hgsp_backloglaborb_sync]
AS
DECLARE @intErrorCode INT
BEGIN TRAN
INSERT INTO BackLogLaborB
SELECT *
FROM BackLogLaborCurrent AS A
WHERE NOT EXISTS ( SELECT *
FROM BackLogLaborB AS B
WHERE A.PIT = B.PIT )
SET NOCOUNT ON;
SELECT @intErrorCode = @@ERROR
IF ( @intErrorCode <> 0 )
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Regards,
David
For the start:
1. If you have single INSERT statement, you don't need to wrap it in TRANSACTION as it's atomic enough by itself.
2. The error you get is not from result of INSERT, but from SET NOCOUNT ON (which is unlikely to fail ;-));
3. I would suggest to learn using T-SQL TRY ... CATCH for error handling.
4. SET NOCOUNT ON; should be the first line in your stored procedure
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply