Savance Enterprise Support

Overview

Array Portion

SQL Database Table Creation And Import Of CSV

First you will open up SQL Management Studio and create a new table called ‘ArrayContacts’.  This table will contain the following columns:

Select the data type and file and click ‘Next’

Verify the column mapping and click ‘Next’

Select the Destination as SQL Server, enter the DB username and Password, click Refresh and select the proper database, then click ‘Next’

Select the ‘ArrayContacts’ table that you created earlier and then click on ‘Edit Mappings’

Match-up the column names as show below and click ‘OK’

Review the mapping and click ‘Next’

Select the ‘Run immediately’ check box and click ‘Finish’ to import the data

SQL Code To Run

Once the csv file has been imported into the ‘ArrayContacts’ table you will then need to run the below script in order to bring the data into the Savance Enterprise database contacts table.

DECLARE @AccountNumber int,

@ContactName varchar(50),

@EmailAddress varchar(100),

@AccountSys int

DECLARE ArrayContactsCursor CURSOR FOR

SELECT ac.AccountNumber,

ac.ContactName,

ac.EmailAddress

FROM ArrayContacts ac

OPEN ArrayContactsCursor

FETCH ArrayContactsCursor INTO @AccountNumber,@ContactName,@EmailAddress

WHILE (@@FETCH_STATUS = 0)

BEGIN

if exists (SELECT AccountSys FROM Accounts where AccountNumber = @AccountNumber AND CurrentValue = 1)

BEGIN

PRINT 'Inserting = ' + @EmailAddress

SELECT @AccountSys = AccountSys FROM Accounts where AccountNumber = @AccountNumber AND CurrentValue = 1

EXEC os_InsertUpdateContact

@ContactSys = NULL,

@AccountSys = @AccountSys,

@UserSys = NULL,

@ContactName = @ContactName,

@FirstName = NULL,

@LastName = NULL,

@Position = NULL,

@Phone = NULL,

@Fax = NULL,

@EmailAddress = @EmailAddress

END

else

BEGIN

PRINT 'NOT Inserting = ' + @EmailAddress

END

FETCH NEXT FROM ArrayContactsCursor INTO @AccountNumber,@ContactName,@EmailAddress

END

CLOSE ArrayContactsCursor

DEALLOCATE ArrayContactsCursor