SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUpdateInsert]
AS
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @ERROR_MESSAGE NVARCHAR(100) = ERROR_MESSAGE();
DECLARE @ERROR_SEVERITY INT = ERROR_SEVERITY();
UPDATE ZIP
SET
ZIP.[ZipCode] = ZC.[PostalCode],
ZIP.[CityID] = C.[ID],
ZIP.[StateID] = S.[ID],
ZIP.[County] = ZC.[County]
FROM dbo.ZipCode ZIP
INNER JOIN [New].dbo.tblZipCode ZC ON ZIP.[ZipCode] = ZC.[PostalCode]
LEFT JOIN [QA].dbo.[city] c on c.name = zc.city
LEFT JOIN [QA].dbo.[state] s on s.name = zc.[State]
--------------------------------------------------------------
SET IDENTITY_INSERT dbo.ZipCode ON
INSERT INTO dbo.ZipCode(
[ID],
[ZipCode],
[CityID],
[StateID],
[County]
)
SELECT
ZC.[PostalCodeID],
ZC.[PostalCode],
C.[ID] AS CityID,
S.[ID] AS StateID,
ZC.[County]
FROM dbo.ZipCode ZIP
RIGHT JOIN [New].dbo.tblZipCode ZC ON ZIP.[ZipCode] = ZC.[PostalCode]
LEFT JOIN [QA].dbo.[city] c on c.name = zc.city
LEFT JOIN [QA].dbo.[state] s on s.name = zc.[State]
WHERE ZIP.[ID] is null
SET IDENTITY_INSERT dbo.ZipCode OFF
--SELECT * FROM [New].dbo.tblZipCode
--SELECT * FROM [QA].dbo.ZipCode
--------------------------------------------------------------
UPDATE ZIP
SET
ZIP.[CityID] = C.[ID],
ZIP.[StateID] = S.[ID]
FROM dbo.ZipCode ZIP
INNER JOIN [DieselDirect_New].dbo.tblZipCode ZC ON ZIP.[ZipCode] = ZC.[PostalCode]
LEFT JOIN [DDBillingPortal_QA].dbo.[city] c on c.name = zc.city
LEFT JOIN [DDBillingPortal_QA].dbo.[state] s on s.name = zc.[State]
COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error
RAISERROR(@ERROR_MESSAGE,@ERROR_SEVERITY, 1)
END CATCH
