This SQL will insert any missing contacts in the “Contact Database” using information found in the Order and Estimate tables.

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

//INSERT Missing Contacts FROM Orders
SELECT ContactID, MAX(OrderID) AS OrderID
FROM "Order Database"
WHERE ContactID IS NOT NULL
      AND OrderDate> '3/20/2020' //Remove this FILTER IF you do NOT have any backup OF the contact DATABASE TO USE AS your basis
      AND ContactID NOT IN (SELECT ContactID FROM "Contact Database")
GROUP BY ContactID
ORDER BY ContactID
; ContactList
 
SELECT ContactID,
       StoreID,
       CustomerID,
       CAST(OrdererFirstName AS CHAR(20)) AS FirstName,
       CAST(OrdererLastName AS CHAR(20)) AS LastName,
       CAST(OrdererTitle AS CHAR(30)) AS Title,
       CAST(OrdererPhoneNumber AS CHAR(15)) AS PhoneNumber,
       CAST(OrdererPhoneExtention AS CHAR(10)) AS PhoneExtention,
       CAST(OrdererFaxNumber AS CHAR(15)) AS FaxNumber,
       CAST(OrdererEmailAddress AS CHAR(75)) AS EmailAddress,
       CAST(OrdererPAreaCode AS CHAR(25)) AS PAreaCode,
       CAST(OrdererFAreaCode AS CHAR(25)) AS FAreaCode,
       CAST("" AS CHAR(25)) AS P2AreaCode,
       CAST("" AS CHAR(15)) AS P2PhoneNumber,
       CAST(0 AS INTEGER) AS P2PhoneType,
       CAST("" AS CHAR(25)) AS P3AreaCode,
       CAST("" AS CHAR(15)) AS P3PhoneNumber,
       CAST(0 AS INTEGER) AS P3PhoneType,
       TRUE AS PrimaryContact,
       TRUE AS ShippingContact,
       TRUE AS APContact,
       TRUE AS InvoicingContact,
       "" AS AlternateAddress,
       CAST(0 AS INTEGER) AS SortOrder,
       CAST(1 AS INTEGER) AS BirthMonth,
       CAST(1 AS INTEGER) AS BirthDay,
       TRUE AS IsActive,
       '' AS ModifiedByComputer,
       '11/17/2021 4:00 PM' AS ModifiedDate
FROM "Order Database"
WHERE OrderID IN (SELECT OrderID FROM ContactList)
ORDER BY ContactID
; LatestOrderContacts FALSE
 
INSERT INTO "Contact Database"
SELECT * FROM LatestOrderContacts
;
 
//INSERT Missing Contacts FROM Estimates
SELECT ContactID, MAX(OrderID) AS OrderID
FROM "Estimate Database"
WHERE ContactID IS NOT NULL
      AND EstimateDate> '3/20/2020'  //Remove this FILTER IF you do NOT have any backup OF the contact DATABASE TO USE AS your basis
      AND ContactID NOT IN (SELECT ContactID FROM "Contact Database")
GROUP BY ContactID
ORDER BY ContactID
; ContactList
 
SELECT ContactID,
       StoreID,
       CustomerID,
       CAST(OrdererFirstName AS CHAR(20)) AS FirstName,
       CAST(OrdererLastName AS CHAR(20)) AS LastName,
       CAST(OrdererTitle AS CHAR(30)) AS Title,
       CAST(OrdererPhoneNumber AS CHAR(15)) AS PhoneNumber,
       CAST(OrdererPhoneExtention AS CHAR(10)) AS PhoneExtention,
       CAST(OrdererFaxNumber AS CHAR(15)) AS FaxNumber,
       CAST(OrdererEmailAddress AS CHAR(75)) AS EmailAddress,
       CAST(OrdererPAreaCode AS CHAR(25)) AS PAreaCode,
       CAST(OrdererFAreaCode AS CHAR(25)) AS FAreaCode,
       CAST("" AS CHAR(25)) AS P2AreaCode,
       CAST("" AS CHAR(15)) AS P2PhoneNumber,
       CAST(0 AS INTEGER) AS P2PhoneType,
       CAST("" AS CHAR(25)) AS P3AreaCode,
       CAST("" AS CHAR(15)) AS P3PhoneNumber,
       CAST(0 AS INTEGER) AS P3PhoneType,
       TRUE AS PrimaryContact,
       TRUE AS ShippingContact,
       TRUE AS APContact,
       TRUE AS InvoicingContact,
       "" AS AlternateAddress,
       CAST(0 AS INTEGER) AS SortOrder,
       CAST(1 AS INTEGER) AS BirthMonth,
       CAST(1 AS INTEGER) AS BirthDay,
       TRUE AS IsActive,
       '' AS ModifiedByComputer,
       '11/17/2021 4:00 PM' AS ModifiedDate
FROM "Estimate Database"
WHERE OrderID IN (SELECT OrderID FROM ContactList)
ORDER BY ContactID
; LatestOrderContacts FALSE
 
INSERT INTO "Contact Database"
SELECT * FROM LatestOrderContacts
;

• Modified: 11/2021
• Version : 8.91