Advices and Best Practices

How to use imported data - recommendations for use

Now that the data is regularly synchronized on ERP Bridge Database, you can see them in a SQL summary table with all data.

Data from HubSpot on the SQL table

You find below an example of stored procedure that you can use for your daily synchronization:

USE [ERPBridge]
GO
/****** Object:  StoredProcedure [dbo].[StoredProcedureName] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[StoredProcedureName] 
AS

BEGIN
	SET NOCOUNT ON;

	DECLARE @CodiceERP varchar(max)
	DECLARE @CompanyName varchar(max)
	DECLARE @StreetAddress varchar(max)
	DECLARE @VAT varchar(max)


	DECLARE cursor_companies CURSOR FOR
    
	select distinct
		ERPCode
		,CompanyName
		,StreetAddress
		,VAT

	from
		ERPBridge.dbo.T_Companies

	where
		LastModifiedDate>GETDATE()-1


	OPEN cursor_companies

	FETCH NEXT FROM cursor_companies INTO @ERPCode, @CompanyName, @StreetAddress, @VAT

	WHILE @@FETCH_STATUS=0
	BEGIN
		IF(@ERPCode IS NULL OR @ERPCode = '')
		BEGIN
			UPDATE NameTable_ERP SET CompanyName_ERP=@CompanyName, StreetAddress_ERP=@StreetAddress, VAT_ERP=@VAT WHERE ERPCode_ERP=@ERPCode	
		END
		ELSE
		BEGIN
			INSERT INTO NameTable_ERP(ERP_Code, CompanyName_ERP, StreetAddress_ERP, VAT_ERP VALUES (@ERPCode, @CompanyName, @StreetAddress, @VAT)
		END

		FETCH NEXT FROM cursor_companies INTO @ERPCode, @CompanyName, @StreetAddress, @VAT
	END

	CLOSE cursor_companies
	DEALLOCATE cursor_companies
END