November 2, 2022 at 8:49 am
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
November 2, 2022 at 11:30 am
Here is one way. It assumes that none of the partner_name columns are NULL – some JOIN tweaking may be required if they are.
DROP TABLE IF EXISTS #stg_partner_names;
CREATE TABLE #stg_partner_names
(
t1_partner1 VARCHAR(100)
,t2_partner2 VARCHAR(100)
,t3_partner3 VARCHAR(100)
,risk_score DECIMAL(10, 2)
);
INSERT #stg_partner_names
(
t1_partner1
,t2_partner2
,t3_partner3
)
VALUES
('Bob', 'Rita', 'Sue');
DROP TABLE IF EXISTS #partner;
CREATE TABLE #partner
(
id INTEGER NOT NULL IDENTITY(1, 1) PRIMARY KEY
,uid INTEGER NULL
,partner_name VARCHAR(255) NULL
);
INSERT #partner
(
partner_name
)
VALUES
('Bob')
,('Rita')
,('Sue');
DROP TABLE IF EXISTS #partner_relationship;
CREATE TABLE #partner_relationship
(
id INTEGER NOT NULL IDENTITY(1, 1) PRIMARY KEY
,partner_from INTEGER NULL
,partner_to INTEGER NULL
);
INSERT #partner_relationship
(
partner_from
,partner_to
)
SELECT partner_from = p3.id
,partner_to = p2.id
FROM #stg_partner_names spn
JOIN #partner p3
ON p3.partner_name = spn.t3_partner3
JOIN #partner p2
ON p2.partner_name = spn.t2_partner2
UNION ALL
SELECT partner_from = p2.id
,partner_to = p1.id
FROM #stg_partner_names spn
JOIN #partner p2
ON p2.partner_name = spn.t2_partner2
JOIN #partner p1
ON p1.partner_name = spn.t1_partner1;
SELECT *
FROM #partner_relationship pr;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 7, 2022 at 7:44 am
This was removed by the editor as SPAM
November 8, 2022 at 10:01 am
This was removed by the editor as SPAM
November 9, 2022 at 7:44 am
This was removed by the editor as SPAM
November 14, 2022 at 10:00 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply