Desmond Chau Desmond Chau - 7 months ago 7
SQL Question

SQL - How to insert values while checking condition based on another table? If null generate new ID

I was given a task to use cursor to duplicate tables. (Don't ask me why or suggest me to use other way cause that's part of the requirement of the task given)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DuplicateCompanyInfo]
@Comp_CompanyId NVARCHAR(80)


AS
BEGIN
SET NOCOUNT ON;

DECLARE @CompanyID NVARCHAR(30),
@PersonID NVARCHAR(30),
@PersonLinkID NVARCHAR(30),
@AddressLinkID NVARCHAR(30),
@AddressID NVARCHAR(30),
@PhoneLinkID NVARCHAR(30),
@PhoneID NVARCHAR(30),
@PersPhoneLinkID NVARCHAR(30),
@PersPhoneID NVARCHAR(30)
EXEC @companyId = crm_next_id 5
EXEC @PersonId = crm_next_id 13
EXEC @AddressId = crm_next_id 1



-- Add Company
INSERT INTO Company
(
Comp_CompanyId, Comp_PrimaryPersonId, Comp_PrimaryAddressId, Comp_Name, Comp_Type, Comp_Status, Comp_CreatedBy,
Comp_CreatedDate, Comp_UpdatedBy, Comp_UpdatedDate, Comp_TimeStamp, Comp_SecTerr, Comp_WebSite
)
SELECT @companyId, @PersonId, @AddressId, Comp_Name, Comp_Type, Comp_Status, '1',
GETDATE(), '1', GETDATE(), GETDATE(), Comp_SecTerr, Comp_WebSite

FROM Company
WHERE Comp_CompanyId = @comp_companyid
AND Comp_Deleted is null



---- PersonLink Cursor ----------------------------------------------
-- Declare Variables
DECLARE @c_PeLi_PersonLinkId NVARCHAR(30)
DECLARE @c_PeLi_PersonId NVARCHAR(30)
DECLARE @c_PeLi_CompanyID NVARCHAR(30)
DECLARE @c_PeLi_CreatedBy NVARCHAR(30)
DECLARE @c_PeLi_CreatedDate NVARCHAR(30)
DECLARE @c_PeLi_UpdatedBy NVARCHAR(30)
DECLARE @c_PeLi_UpdatedDate NVARCHAR(30)
DECLARE @c_PeLi_TimeStamp NVARCHAR(30)


--Declare Cursor
DECLARE PersonLinkCursor CURSOR FOR
SELECT PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy,
PeLi_UpdatedDate, PeLi_TimeStamp
FROM Person_Link
INNER JOIN Person
ON PeLi_PersonId = Pers_PersonId
AND PeLi_CompanyID = @comp_companyid
AND Pers_Deleted is null
AND PeLi_Deleted is null

--Open Cursor & fetch 1st row into variables
OPEN PersonLinkCursor
FETCH NEXT FROM PersonLinkCursor INTO @c_PeLi_PersonLinkId, @c_PeLi_PersonId, @c_PeLi_CompanyID, @c_PeLi_CreatedBy,
@c_PeLi_CreatedDate, @c_PeLi_UpdatedBy, @c_PeLi_UpdatedDate, @c_PeLi_TimeStamp


--Fetch successful
--Check for a new row
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC @c_PeLi_PersonLinkId = crm_next_id 31
EXEC @PersonId = crm_next_id 13


INSERT INTO Person_Link
(
PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy,
PeLi_UpdatedDate, PeLi_TimeStamp
)
VALUES
(
@c_PeLi_PersonLinkId, @PersonId, @companyId, '1', GETDATE(), '1', GETDATE(), GETDATE()
)


--Get next available row into variables
FETCH NEXT FROM PersonLinkCursor INTO @c_PeLi_PersonLinkId, @c_PeLi_PersonId, @c_PeLi_CompanyID, @c_PeLi_CreatedBy,
@c_PeLi_CreatedDate, @c_PeLi_UpdatedBy, @c_PeLi_UpdatedDate, @c_PeLi_TimeStamp

END

CLOSE PersonLinkCursor
DEALLOCATE PersonLinkCursor



---- Person Cursor ----------------------------------------------
-- Declare Variables
DECLARE @c_Pers_PersonId NVARCHAR(30)
DECLARE @c_Pers_CompanyId NVARCHAR(30)
DECLARE @c_Pers_PrimaryUserId NVARCHAR(30)
DECLARE @c_Pers_FirstName NVARCHAR(30)
DECLARE @c_Pers_SecTerr NVARCHAR(30)
DECLARE @c_Pers_CreatedBy NVARCHAR(30)
DECLARE @c_Pers_CreatedDate NVARCHAR(30)
DECLARE @c_Pers_UpdatedBy NVARCHAR(30)
DECLARE @c_Pers_UpdatedDate NVARCHAR(30)
DECLARE @c_Pers_TimeStamp NVARCHAR(30)


--Declare Cursor
DECLARE PersonCursor CURSOR FOR
SELECT Pers_PersonId, Pers_CompanyId, Pers_PrimaryUserId, Pers_FirstName, Pers_SecTerr, Pers_CreatedBy,
Pers_CreatedDate, Pers_UpdatedBy, Pers_UpdatedDate, Pers_TimeStamp
FROM Person
INNER JOIN Person_Link
ON Pers_PersonId = PeLi_PersonId
AND PeLi_CompanyID = @comp_companyid
AND Pers_Deleted is null
AND PeLi_Deleted is null


--Open Cursor & fetch 1st row into variables
OPEN PersonCursor
FETCH NEXT FROM PersonCursor INTO @c_Pers_PersonId, @c_Pers_CompanyId, @c_Pers_PrimaryUserId, @c_Pers_FirstName,
@c_Pers_SecTerr, @c_Pers_CreatedBy, @c_Pers_CreatedDate, @c_Pers_UpdatedBy,
@c_Pers_UpdatedDate, @c_Pers_TimeStamp


--Fetch successful
--Check for a new row
WHILE @@FETCH_STATUS = 0
BEGIN

EXEC @PersonId = crm_next_id 13


INSERT INTO Person
(
Pers_PersonId, Pers_CompanyId, Pers_PrimaryUserId, Pers_FirstName, Pers_SecTerr, Pers_CreatedBy,
Pers_CreatedDate, Pers_UpdatedBy, Pers_UpdatedDate, Pers_TimeStamp
)
VALUES
(
@PersonId, @companyId, @c_Pers_PrimaryUserId, @c_Pers_FirstName, @c_Pers_SecTerr, '1',
GETDATE(), '1', GETDATE(), GETDATE()
)


--Get next available row into variables
FETCH NEXT FROM PersonCursor INTO @c_Pers_PersonId, @c_Pers_CompanyId, @c_Pers_PrimaryUserId, @c_Pers_FirstName,
@c_Pers_SecTerr, @c_Pers_CreatedBy, @c_Pers_CreatedDate, @c_Pers_UpdatedBy,
@c_Pers_UpdatedDate, @c_Pers_TimeStamp

END

CLOSE PersonCursor
DEALLOCATE PersonCursor

END


This is my table structure:

Company Table:

comp_companyid | comp_primarypersonid | comp_primaryaddressid | comp_name
-------------------------------------------------------------------------
2 | 2 | 2 | company 2
3 | 3 | 3 | company 3


Person Table:

pers_personid | pers_companyid | pers_primaryaddressid | pers_name
-------------------------------------------------------------------------
2 | 2 | 2 | person 2
3 | 3 | 3 | person 3
4 | 2 | 2 | person 4


Person Link Table:

peli_personlinkid | peli_personid | peli_companyid
-------------------------------------------------------------------------
2 | 2 | 2
3 | 3 | 3
4 | 4 | 2


Now I'm inserting values into each table in the cursor loop as shown above. I would like to insert the value of
PeLi_PersonID
from
PersonLink
&
Pers_PersonID
from
Person
based on the column
Comp_PrimaryPersonId
from
Company
on something like if the ID exists, use the ID, else generate the next id. How do I do that? Thanks!

I've tried this:

INSERT INTO Person_Link
(
PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy,
PeLi_UpdatedDate, PeLi_TimeStamp
)
VALUES
(
@c_PeLi_PersonLinkId,(SELECT @PersonID FROM Company WHERE Comp_PrimaryPersonId is not null AND Comp_CompanyId = @Comp_CompanyId),
@companyId, '1', GETDATE(), '1', GETDATE(), GETDATE()
)


It succeeded in taking the value of person id from company's
Comp_PrimaryPersonID
but the
PeLi_PersonId
duplicated will not generate new ID. How can I do a proper condition check? I would like to do a condition check within the loop that defines: if
Comp_PrimaryPersonID
's value exists, use the value, if not get next id.Thanks!

Answer

Same case as in prior step - put them into single cursor. First - copy Person and obtain new personid, second - insert Person_Link with link to newly created person.

If you need to create another Person_Link (which is strange because you just copied all of Person_Links) to some "primary" person - you need new Person_Id.

(SELECT @PersonID FROM Company 
WHERE Comp_PrimaryPersonId is not null AND Comp_CompanyId = @Comp_CompanyId)

In this select you refer old @Comp_CompanyId so you could obtain only old Person_Id while you are needing exactly new one (which can be found under @companyId id).

But you will not obtain even old id with this select - because it selects constant value of @PersonID variable which is not even initialized before this select thus contains some garbage from previous places of usage.

Anyway, you already lost relation with primary person for new company at that moment because of the very first insert into Company - its newly generated Comp_PrimaryPersonId value links to nothing.

One way to solve it is to check inside of cursor whether @c_PeLi_PersonId is same as old companies' Comp_PrimaryPersonId and to update new companies' Comp_PrimaryPersonId field with @PersonId value. Store old companies' Comp_PrimaryPersonId into variable at the beginning of your script and use it in cursor.