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?
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[hgsp_backloglaborb_sync]
DECLARE @intErrorCode INT
INSERT INTO BackLogLaborB
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 )
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
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help