Archive

Archive for April, 2013

Insert Non-Duplicate record into table

April 30, 2013 Leave a comment

Insert record into table from another source but igonre those who are already exists.
Put a Outer Join and check for ID Not NULL Becuase those record ,who has already inserted into table has a valid ID. AND null means that these records are not inserted

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spName]’) AND type in (N’P’, N’PC’))
BEGIN
PRINT ‘DROPPING PROCEDURE [dbo].[spName]’
DROP PROCEDURE [dbo].[spName]
END
GO

IF OBJECT_ID(‘tempdb..#TempFee’) IS NOT NULL
BEGIN
DROP TABLE #TempFee
END
CREATE TABLE #TempFee(
–columns……
) ON [PRIMARY]

DECLARE @FeeID INT
IF @FeeID > 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION

INSERT INTO Table1(columns….)
SELECT columns …….FROM #TempFee WITH (NOLOCK)
INNER JOIN Table2 c WITH (NOLOCK) ON nfi.ID= c.ID AND c.State = @STATE
LEFT OUTER JOIN table 3 cfc ON cfc.Id = cl.ID
AND cfc.Active = 1
WHERE nfi.ID IS NOT NULL AND cfc.Id IS NULL

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorMessage VARCHAR(1000)

SELECT @ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorMessage = ERROR_MESSAGE()

RAISERROR (@ErrorNumber, @ErrorSeverity, @ErrorState)
END CATCH
END

Categories: SQL