WARNING: This is not kids stuff. Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine. If you are interested in using sql_bridge but are not a SQL guru, please contact a sales or consulting at Cyrious.

Note: This feature requires Control 5.75 or higher.

The stored procedure can be used to Import Customers and Contact from a CSV file into Control.

Before executing the Stored Procedure, you must add your customer list as a table. It is recommended to use the SQL Import Wizard to build your table.

• Log into SQL Management Studio
• Right-click on your database instance (i.e. StoreData), and navigate to Tasks > Import Data to open the SQL Server Import and Export Wizard.
• Set the following information on the “Choose a Data Source” screen:
  • Set your data source to Flat File Source.
  • Click Browse to find your CSV.
  • Click Columns to preview your data.
  • Click Advance to set the Data Types and OutputColumnWidth
  • Click Next
• Set the following information on the “Choose a Destination” screen:
  • Set your destination to SQL Server.
  • Verify your Server Name
  • Enter credentials for your SQL Server Authentication
  • Verify your Database
  • Click Next
• Set your Table Name. (It will default to your CSV file name) Click Next.
• Make “Run immediately” is checked. Click Next. Depending on your version of SQL, you may have additional permissions to save before running.
• Click Finish.

Your table should now be added to the database.

Notes:

• If the Customer or Contact already exist, they are updated.
• If the Customer and Contact do not exist, they are imported.
• If the Customer exists but not the Contact, the Customer is updated, and the contact is imported.

The stored procedure requires sql_bridge in order to obtain IDs for the inserted records. sql_bridge is a collection of SQL Server stored procedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.

This imports and updates all Customers and Contacts.

EXEC csp_ImportCustomerList
;

The SQL to create the Customer List Import Function.

• This must be run to create the stored procedure before it can be used.
• This function also requires two additional SQL procedures found below. They must be run to create those functions before this method can be used.

-- =============================================
-- Author:          Cyrious Sofware
-- Create date:     November-2016
-- Description:     This stored procedure imports a Customer CVS file with the
--                  following information:
--
--                  Customer
--                  Contacts
--
-- Notes:
--          ** If the Customer or Contact already exist, they are updated.
--          ** If the Customer and Contact do not exist, they are imported.
--          ** If the customer exists but not the contact, the customer is updated, and the contact is imported.
--
--          The import assumes you have had the customer/contact list as a table.
--          It is recommended to use the SQL Import Wizard to build your table.
--
-- To Enable the File Copy, you must enable SQL to run a command shell.
-- To do this, run the following commands (in order) in SQL Server Management Console.
--
-- ---- To allow advanced options to be changed.
-- EXEC sp_configure 'show advanced options', 1
-- GO
-- ---- To update the currently configured value for advanced options.
-- RECONFIGURE
-- GO
-- ---- To enable the feature.
-- EXEC sp_configure 'xp_cmdshell',1
-- GO
-- ---- To update the currently configured value for this feature.
-- RECONFIGURE
-- GO
--
-- Returns:     Table of new records
-- =============================================
ALTER PROCEDURE [dbo].[csp_ImportCustomerList]
 
-- Optional inputs - Leave off for the default value or if they don't apply
@DeveloperMode      bit             = 0         -- Show addtional information helpful for development
 
AS
BEGIN
-- Define some variables used in the import
DECLARE @Logs                               TABLE( ID INT, ClassTypeID INT, ParentID INT, IsError BIT,
Summary VARCHAR(255),
Detail VARCHAR(2000)
);
DECLARE @NewCompanies                       SMALLINT = 0;
DECLARE @NewContacts                        SMALLINT = 0;
 
DECLARE @AccountID                          INT;
DECLARE @ContactID                          INT;
DECLARE @CreateCompany                      BIT;
DECLARE @CreateContact                      BIT;
 
DECLARE @RowID                              SMALLINT;
DECLARE @CompanyName                        VARCHAR(128);
 
DECLARE @WorkPhoneAC                        VARCHAR(25);
DECLARE @WorkPhoneNumber                    VARCHAR(25);
DECLARE @WorkExtension                      VARCHAR(25);
 
DECLARE @BillingAddress1                    VARCHAR(256);
DECLARE @BillingAddress2                    VARCHAR(256);
DECLARE @BillingCity                        VARCHAR(256);
DECLARE @BillingState                       VARCHAR(256);
DECLARE @BillingPostalCode                  VARCHAR(256);
 
DECLARE @FirstName                          VARCHAR(25);
DECLARE @LastName                           VARCHAR(25);
DECLARE @POSITION                              VARCHAR(10);
DECLARE @IsPrimary                          BIT;
DECLARE @EmailAddress                       VARCHAR(50);
 
DECLARE @CellPhoneAC                        VARCHAR(10);
DECLARE @CellPhoneNumber                    VARCHAR(25);
 
DECLARE @SalespersonID                      INT;
DECLARE @SalespersonName                    VARCHAR(255);
 
SET NOCOUNT ON;
 
-- Define some error variables in case we need them
DECLARE @DT              SMALLDATETIME = GetDate();
DECLARE @ComputerName    VARCHAR(25) = @@ServerName;
DECLARE @NewLine         CHAR(2) =  CHAR(10)+CHAR(13);
DECLARE @Pos             INT = 0;
 
 
-------------------------------------------------------------------
-- Import the Customers
-------------------------------------------------------------------
--
-- Loop through the customer data in the inmport file and create them
--
DECLARE CustomerList CURSOR FOR
SELECT
CONVERT(INT, AccountID)         ,   -- AccountID
CompanyName                     ,   -- CompanyName
FirstName                       ,   -- Contact First Name
LastName                        ,   -- Contact Last Name
Title                           ,   -- Contact Title
CONVERT(Bit, IsPrimary)         ,   -- Is Primary Contact
Address1                        ,   -- Billing StreetAddress1
Address2                        ,   -- Billing StreetAddress2
City                            ,   -- Billing City
State                           ,   -- Billing State
PostalCode                      ,   -- Billing Postal Code
MainAreaCode                    ,   -- WorkPhoneAC
MainPhoneNumber                 ,   -- WorkPhoneNumber
MainExtension                   ,   -- WorkExtension
MobileAreaCode                  ,   -- Contact CellPhoneAC
MobilePhoneNumber               ,   -- Contact CellPhoneNumber
Email                           ,   -- Contact Email
Salesperson                     ,   -- Company Salesperson1 Name
CONVERT(INT, SalespersonID)         -- Employee ID
FROM ContactListImport;  -- Replace with the name of your SQL Table holding your data.
 
OPEN CustomerList;
FETCH NEXT FROM CustomerList INTO
@AccountID        ,
@CompanyName      ,
@FirstName        ,
@LastName         ,
@POSITION         ,
@IsPrimary        ,
@BillingAddress1  ,
@BillingAddress2  ,
@BillingCity      ,
@BillingState     ,
@BillingPostalCode        ,
@WorkPhoneAC      ,
@WorkPhoneNumber  ,
@WorkExtension    ,
@CellPhoneAC      ,
@CellPhoneNumber  ,
@EmailAddress     ,
@SalespersonName  ,
@SalespersonID    ;
 
WHILE @@FETCH_STATUS = 0
BEGIN
-- For each customer, create a separate transaction
BEGIN TRANSACTION
BEGIN TRY
PRINT @CompanyName;
 
 
SELECT TOP(1)
@AccountID = A.ID
FROM Account A
WHERE A.CompanyName = @CompanyName;
 
SELECT TOP(1)
@ContactID = AC.ID
FROM AccountContact AC
WHERE AC.AccountID = @AccountID;
 
SET @FirstName            = (CASE WHEN COALESCE(@FirstName, '') = '' THEN '.' ELSE @FirstName END);
SET @LastName             = (CASE WHEN COALESCE(@LastName , '') = '' THEN '.' ELSE @LastName  END);
 
 
IF (@AccountID IS NULL) -- then insert the company with the contact
BEGIN
INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
EXEC csp_ImportCompany
 
-- OUTPUT Variables to Capture the Key
@AccountID            = @AccountID    OUTPUT,
@ContactID            = @ContactID    OUTPUT,
 
-- Input Variables
@CompanyName        = @CompanyName,
@SalespersonID      = @SalespersonID,
 
@WorkPhoneAC        = @WorkPhoneAC,
@WorkPhoneNumber    = @WorkPhoneNumber,
@WorkExtension      = @WorkExtension,
 
-- Company Billing Address Info
@BillingAddress1    = @BillingAddress1,
@BillingAddress2    = @BillingAddress2,
@BillingCity        = @BillingCity,
@BillingState       = @BillingState,
@BillingPostalCode  = @BillingPostalCode,
 
-- Contact #1 Information
@AddContact         = 1,
@FirstName          = @FirstName,
@LastName           = @LastName,
@EmailAddress       = @EmailAddress,
 
@CellPhoneAC        = @CellPhoneAC,
@CellPhoneNumber    = @CellPhoneNumber
;
SET @NewCompanies = @NewCompanies + 1;
SET @NewContacts  = @NewContacts  + 1;
 
UPDATE ContactListImport  -- Replace with the name of your SQL Table holding your data.
SET   AccountID = @AccountID
WHERE CompanyName = @CompanyName
;
 
PRINT '    Company and Contact Created';
END
 
ELSE IF (@ContactID IS NULL) -- then just add the contact
BEGIN
 
INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
EXEC csp_ImportContact
 
-- OUTPUT Variables to Capture the Key
@ContactID            = @ContactID    OUTPUT,
 
-- INPUT Variables
@AccountID            = @AccountID,
@SalespersonID        = @SalespersonID,
 
@FirstName            = @FirstName,
@LastName             = @LastName,
@EmailAddress         = @EmailAddress,
@POSITION             = @POSITION,
 
@CellPhoneAC        = @CellPhoneAC,
@CellPhoneNumber    = @CellPhoneNumber,
 
@UseDefaultPhone    = 0,
@WorkPhoneAC        = @WorkPhoneAC,
@WorkPhoneNumber    = @WorkPhoneNumber,
@WorkExtension      = @WorkExtension
 
;
SET @NewContacts  = @NewContacts  + 1;
 
PRINT '    Company Exists; Contact Created';
END
 
ELSE
BEGIN
IF (@AccountID IS NOT NULL)
--update the company and contact with the new info from the table
 
--INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
 
-- Now update the Account
-- ----------------------------------
UPDATE PhoneNumber
SET SeqID               = SeqID + 1,
AreaCode            = @WorkPhoneAC,
PhoneNumber         = @WorkPhoneNumber,
Extension           = @WorkExtension,
FormattedText       = '('+@WorkPhoneAC+') '+ @WorkPhoneNumber + ' x' + @WorkExtension
WHERE PhoneNumber.ID = (SELECT A.MainPhoneNumberID FROM Account A WHERE A.ID = @AccountID)
;
 
UPDATE Address
SET SeqID             = SeqID + 1,
StreetAddress1    = @BillingAddress1,
StreetAddress2    = @BillingAddress2,
City              = @BillingCity,
State             = @BillingState,
PostalCode        = @BillingPostalCode,
FormattedText     = @BillingAddress1  + @NewLine + (CASE WHEN len(COALESCE(@BillingAddress2, '')) > 1 THEN @BillingAddress2 + @NewLine ELSE '' END)
+ @BillingCity + ', ' + @BillingState + '  ' + @BillingPostalCode
WHERE Address.ID = (SELECT A.BillingAddressID FROM Account A WHERE A.ID = @AccountID)
;
 
UPDATE Account
SET SeqID               = SeqID + 1,
CompanyName         = @CompanyName,
SalesPersonID1      = @SalespersonID,
PrimaryNumber       = '('+@WorkPhoneAC+') '+ @WorkPhoneNumber + ' x' + @WorkExtension
WHERE Account.ID = @AccountID
;
 
UPDATE ContactListImport  -- Replace with the name of your SQL Table holding your data.
SET   AccountID = @AccountID
WHERE CompanyName = @CompanyName
;
 
-- Now update the AccountContact
-- ----------------------------------
UPDATE AccountContact
SET
FirstName            = @FirstName,
LastName             = @LastName,
POSITION             = @POSITION,
EmailAddress         = @EmailAddress,
IsPrimaryContact     = @IsPrimary,
SalesPersonID1       = @SalespersonID,
PrimaryNumber        = '('+@WorkPhoneAC+') '+ @WorkPhoneNumber + ' x' + @WorkExtension,
SecondaryNumber      = '('+@CellPhoneAC+') '+ @CellPhoneNumber
WHERE AccountContact.ID = @ContactID
;
 
 
 
END;
 
-- and reset the variables for the next time
SET @AccountID = NULL;
SET @ContactID = NULL;
 
PRINT '    Company and Contact Updated';
 
-- Make it so, #1
COMMIT TRANSACTION;
 
END TRY
 
BEGIN CATCH
ROLLBACK TRANSACTION;
 
INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
VALUES(
@AccountID, 2000, NULL, 1,
'Company '+@CompanyName+'  Import FAILED due to Unhandled Exception.',
'Exception: '+ERROR_MESSAGE()
);
 
END CATCH;
 
-- Now continue to the next customer
FETCH NEXT FROM CustomerList INTO
@AccountID        ,
@CompanyName      ,
@FirstName        ,
@LastName         ,
@POSITION         ,
@IsPrimary        ,
@BillingAddress1  ,
@BillingAddress2  ,
@BillingCity      ,
@BillingState     ,
@BillingPostalCode        ,
@WorkPhoneAC      ,
@WorkPhoneNumber  ,
@WorkExtension    ,
@CellPhoneAC      ,
@CellPhoneNumber  ,
@EmailAddress     ,
@SalespersonName  ,
@SalespersonID    ;
END;
 
CLOSE CustomerList;
DEALLOCATE CustomerList;
 
 
-------------------------------------------------------------------
---- Output an Informational Summary of Results
-------------------------------------------------------------------
--
IF (@DeveloperMode = 1)
BEGIN
SELECT (CASE WHEN IsError = 1 THEN 'ERROR' ELSE 'Information' END) AS [Message TYPE], *
FROM @Logs
ORDER BY IsError DESC, ClassTypeID, COALESCE(ParentID, ID);
 
SELECT * FROM ContactListImport;  -- Replace with the name of your SQL Table holding your data.
 
END
ELSE
BEGIN
SELECT (CASE WHEN IsError = 1 THEN 'ERROR' ELSE 'Information' END) AS [Message TYPE],
Summary, ParentID, ID, ClassTypeID, Detail
FROM @Logs
ORDER BY IsError DESC, ClassTypeID, COALESCE(ParentID, ID);
END;
 
END;

Contributor: Cyrious Software
Date: 11/2016
Version: Control 5.7+