Support Suite

Knowledgebase: Array

How To Import Contacts Into Savance Enterprise From Array

Posted by Michael Renock on 16 September 2014 02:02 PM

Overview

This knowledge base will show you how to get contact data out of Array and manually import it into the Savance Enterprise database. It is assumed that you have prior knowledge of how to execute a CQ program on Array, as well as how to use use SQL Management Studio.

Array Portion

Run this Array CQ program to export the data to a CSV file.

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:

Once the table is created, you will need to use the SQL Import Export Data Tool, which can be launched from the Start Menu.

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

Savance | The Company

Savance sets ourselves apart from our competition by keeping it personal. We actively request feedback, know our customers by first name, integrate their ideas into our products, and measure our success based on their happiness. The way businesses used to be run.