Do I need self joins, cte?

1

Hi, I am struggling with a merge statement. I have a staging table with two names in it. I've simplified it for this but its
Create Table stg_partner_names
( t1_partner1 varchar(100)
,t2_partner2 varchar(100)
,t3_partner3 varchar(100)
, risk_score decimal (10,2))

insert into stg_partner_names (t1_partner1,t2_partner2,t3_partner3) VALUES( 'Bob','Rita', 'Sue')

so the hierarchy is t3 reports to t2 and t2 reports to t1

I want to populate a partner relationship table using these values to lookup the id for each of the names

create table partner_relationship (
id integer not null IDENTITY(1,1) PRIMARY KEY,
--partner_relationship_type_id integer null,
partner_from integer null,
partner_to integer null)

The ids for each partner are here, so I want to return the ids for each partner using the partner_name:

create table partner (
id integer not null IDENTITY(1,1) PRIMARY KEY,
uid integer null,
partner_name varchar(255) null)

insert into partner (partner_name) VALUES('Bob')
insert into partner (partner_name) VALUES('Rita')
insert into partner (partner_name) VALUES('Sue')

I would expect to see in partner_relationship once Ive inserted

id, partner_from partner_to
1 3 2
2 2 1

Do I need self joins , cte ? Would l it be better to do it as two separate inserts for each 'tier'?

Thanks for any help in advance

2

Assuming partner_name is unique:

MERGE dbo.partner_relationship T
USING
(
	SELECT DISTINCT PF.id AS partner_from, PT.id AS partner_to
	FROM dbo.stg_partner_names N
		CROSS APPLY
		(
			VALUES (N.t3_partner3, N.t2_partner2)
				,(N.t2_partner2, N.t1_partner1)
		) X (partner_from, partner_to)
		JOIN dbo.[partner] PF
			ON X.partner_from = PF.partner_name
		JOIN dbo.[partner] PT
			ON X.partner_to = PT.partner_name
) S
ON T.partner_from = S.partner_from
	AND T.partner_to = S.partner_to
WHEN NOT MATCHED BY TARGET
THEN INSERT (partner_from, partner_to)
	VALUES (S.partner_from, S.partner_to)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;

It may be better to do separate INSERTS and DELETES as MERGE can cause a lot of blocking.
I would also not have an IDENTITY in dbo.partner_relationship but CREATE the table like:

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE dbo.partner_relationship
(
	partner_from int NOT NULL
	,partner_to int NOT NULL
	,CONSTRAINT partner_relationship_PK PRIMARY KEY (partner_from, partner_to)
);
GO
1 Like
3

Hi, yes that works perfectly, thank you.

4

Good.

If you are using MERGE in SQL Server you need to be aware of articles like this.

Personally I would put the code from the USING in a view and do separate INSERT and DELETE statements.