SQL Script To Insert Creditor Bank Details From CSV

Integration Manager is a very good tool, but it doesn’t allow the import of all record types into Microsoft Dynamics GP. One of the main types of record I need to import when implementing a new client is the Creditor (Vendor to the American readers) EFT details.

While the EFT data is stored in one table and can therefore be imported using Table Import, I find that configuring Table Import for each client is a bit of a annoyance, so I developed a SQL Script a while ago which I figured I might as well post here for easy access.

As always when using SQL to update tables in Microsoft Dynamics GP, make sure you have a good backup of the database before you begin and check the imported data afterwards. Read on for the script…

CREATE TABLE #AddressElectronicFundsTransferMaster
	(VENDORID VARCHAR(15)
	,ADRSCODE VARCHAR(15)
	,EFTBankCode VARCHAR(6)
	,EFTBankAcct VARCHAR(8))
GO

BULK INSERT
	#AddressElectronicFundsTransferMaster
FROM
	'R:\DynamicsCentral\IM\CreditorBankDetails.csv'
WITH
	(FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n')
GO

INSERT INTO SY06000
	(AddressEFTMaster.SERIES
	,AddressEFTMaster.CustomerVendor_ID 
	,AddressEFTMaster.ADRSCODE
	,AddressEFTMaster.VENDORID
	,AddressEFTMaster.CUSTNMBR
	,AddressEFTMaster.EFTUseMasterID
	,AddressEFTMaster.EFTBankType
	,AddressEFTMaster.FRGNBANK
	,AddressEFTMaster.INACTIVE
	,AddressEFTMaster.BANKNAME
	,AddressEFTMaster.EFTBankAcct
	,AddressEFTMaster.EFTBankBranch
	,AddressEFTMaster.GIROPostType
	,AddressEFTMaster.EFTBankCode
	,AddressEFTMaster.EFTBankBranchCode
	,AddressEFTMaster.EFTBankCheckDigit
	,AddressEFTMaster.BSROLLNO
	,AddressEFTMaster.IntlBankAcctNum
	,AddressEFTMaster.SWIFTADDR
	,AddressEFTMaster.CustVendCountryCode
	,AddressEFTMaster.DeliveryCountryCode
	,AddressEFTMaster.BNKCTRCD
	,AddressEFTMaster.CBANKCD
	,AddressEFTMaster.ADDRESS1
	,AddressEFTMaster.ADDRESS2
	,AddressEFTMaster.ADDRESS3
	,AddressEFTMaster.ADDRESS4
	,AddressEFTMaster.RegCode1
	,AddressEFTMaster.RegCode2
	,AddressEFTMaster.BankInfo7
	,AddressEFTMaster.EFTTransitRoutingNo
	,AddressEFTMaster.CURNCYID
	,AddressEFTMaster.EFTTransferMethod
	,AddressEFTMaster.EFTAccountType
	,AddressEFTMaster.EFTPrenoteDate
	,AddressEFTMaster.EFTTerminationDate)

	(SELECT
		4
		,VENDORID
		,ADRSCODE
		,VENDORID
		,''
		,1
		,3
		,0
		,0
		,' '
		,EFTBankAcct
		,''
		,0
		,EFTBankCode
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,''
		,0
		,''
		,''
		,1
		,1
		,'1900-01-01 00:00:00.000'
		,'1900-01-01 00:00:00.000'
	FROM
		#AddressElectronicFundsTransferMaster WHERE LEN(EFTBankAcct) > 0)
GO

DROP TABLE #AddressElectronicFundsTransferMaster
GO