CODEWITHSUNDEEP
sqlsql-serverselectinsertbulkinsert
Metalshark
Metalshark

Reputation: 8482

Dependent Insert with Multi Row Select

We have a CRM system using SQL Server and are looking to populate all customers with an email address, even if that email address is NULL.

There is a Customer table, a table for Email addresses and a Linker table.

I can perform a SELECT query for all customers who need a new Email row:

SELECT @CompanyId = Comp_CompanyId
FROM Company
WHERE Company.Comp_CompanyId NOT IN (
  SELECT ELink_RecordID
  FROM EmailLink
)

and I can perform the INSERT statements to add an Email address to a customer:

DECLARE @EmailId int
DECLARE @ELinkId int

EXEC @ELinkId = eware_get_identity_id 'Email'
EXEC @EmailId = eware_get_identity_id 'Email'

INSERT INTO Email (
  Emai_EmailId,
  Emai_CreatedBy,
  Emai_CreatedDate,
  Emai_UpdatedBy,
  Emai_UpdatedDate,
  Emai_TimeStamp,
  Emai_EmailAddress
)
VALUES (
  @EmailId,
  1,
  GETDATE(),
  1,
  GETDATE(),
  GETDATE(),
  NULL
)
INSERT INTO EmailLink(
  ELink_LinkID,
  ELink_CreatedBy,
  ELink_CreatedDate,
  ELink_UpdatedBy,
  ELink_UpdatedDate,
  ELink_TimeStamp,
  ELink_EntityID,
  ELink_RecordID,
  ELink_Type,
  ELink_EmailId
)
VALUES(
  @ELinkId,
  1,
  GETDATE(),
  1,
  GETDATE(),
  GETDATE(),
  5,          -- Person is 13, Company is 5
  COMPANY_ID_GOES_HERE,
  'Business',
  @EmailId
)

Is there a way I can combine these into one big SQL statement without having to use an external tool? I can write a quick Python program to do this, but the people maintaining this longterm are SQL focused.

The furthest I've got is combining the SELECT with the INSERT for the email, but couldn't figure out how to then INSERT the linker row:

DECLARE @CompanyId int
DECLARE @EmailId int
DECLARE @ELinkId int

EXEC @ELinkId = eware_get_identity_id 'Email'
EXEC @EmailId = eware_get_identity_id 'Email'

INSERT INTO Email (
  Emai_EmailId,
  Emai_CreatedBy,
  Emai_CreatedDate,
  Emai_UpdatedBy,
  Emai_UpdatedDate,
  Emai_TimeStamp,
  Emai_EmailAddress
)
VALUES (
  @EmailId,
  1,
  GETDATE(),
  1,
  GETDATE(),
  GETDATE(),
  NULL
)

SELECT @CompanyId = Comp_CompanyId
FROM Company
WHERE Company.Comp_CompanyId NOT IN (
  SELECT ELink_RecordID
  FROM EmailLink
)

Upvotes: 0

Views: 45

Answers (1)

Mazhar
Mazhar

Reputation: 3837

Will this work for the second part?

DECLARE @CompanyId int
DECLARE @EmailId int
DECLARE @ELinkId int

EXEC @ELinkId = eware_get_identity_id 'Email'
EXEC @EmailId = eware_get_identity_id 'Email'

INSERT INTO Email (
  Emai_EmailId,
  Emai_CreatedBy,
  Emai_CreatedDate,
  Emai_UpdatedBy,
  Emai_UpdatedDate,
  Emai_TimeStamp,
  Emai_EmailAddress
)
VALUES (
  @EmailId,
  1,
  GETDATE(),
  1,
  GETDATE(),
  GETDATE(),
  NULL
)
;WITH cteX
AS(
    SELECT CompanyId = Comp_CompanyId
    FROM Company
    WHERE Company.Comp_CompanyId 
    NOT IN (
            SELECT ELink_RecordID
            FROM EmailLink 
            )
)
INSERT INTO EmailLink(
  ELink_LinkID,
  ELink_CreatedBy,
  ELink_CreatedDate,
  ELink_UpdatedBy,
  ELink_UpdatedDate,
  ELink_TimeStamp,
  ELink_EntityID,
  ELink_RecordID,
  ELink_Type,
  ELink_EmailId
)
SELECT 
    @ELinkId,
    1,
    GETDATE(),
    1,
    GETDATE(),
    GETDATE(),
    5,          -- Person is 13, Company is 5
    X.CompanyId, --COMPANY_ID_GOES_HERE,
    'Business',
    @EmailId
FROM 
    cteX X

Upvotes: 2

Related Questions