I hope you guys could help me with solving the following problem.
I am new to SQL (using SQL Server Management Studio 2017).
My goals is to combine the keys of different tables into one table.
Which is shown below. For this example i have 4 source tables of which i only display the keys;
ContractTable:
+------------+---------+
| ContractId | PartyId |
+------------+---------+
| C01 | 1 |
+------------+---------+
| C02 | 2 |
+------------+---------+
| C03 | 3 |
+------------+---------+
| C04 | 4 |
+------------+---------+
| C05 | NULL |
+------------+---------+
<!-- end snippet -->
PartyTable:
+---------+
| PartyId |
+---------+
| P1 |
+---------+
| P2 |
+---------+
| P3 |
+---------+
| P4 |
+---------+
| P5 |
+---------+
| P6 |
+---------+
| P7 |
+---------+
| P8 |
+---------+
RentalObjectTable:
+----------------+------------+----------+
| RentalObjectId | ContractId | ObjectId |
+----------------+------------+----------+
| R1 | C5 | O1 |
+----------------+------------+----------+
| R2 | NULL | O2 |
+----------------+------------+----------+
| R3 | C4 | O3 |
+----------------+------------+----------+
| R4 | C1 | O4 |
+----------------+------------+----------+
ObjectTable:
+----------+
| ObjectId |
+----------+
| O1 |
+----------+
| O2 |
+----------+
| O3 |
+----------+
| O4 |
+----------+
| O5 |
+----------+
| O6 |
+----------+
Together they should form the Key/FactTable
+---------+------------+----------------+----------+
| PartyId | ContractId | RentalObjectId | ObjectId |
+---------+------------+----------------+----------+
| P1 | C01 | R4 | O4 |
+---------+------------+----------------+----------+
| P2 | C02 | NULL | NULL |
+---------+------------+----------------+----------+
| P3 | C03 | NULL | NULL |
+---------+------------+----------------+----------+
| P4 | C04 | R3 | O3 |
+---------+------------+----------------+----------+
| P5 | NULL | NULL | NULL |
+---------+------------+----------------+----------+
| P6 | NULL | NULL | NULL |
+---------+------------+----------------+----------+
| P7 | NULL | NULL | NULL |
+---------+------------+----------------+----------+
| P8 | NULL | NULL | NULL |
+---------+------------+----------------+----------+
| NULL | C5 | R1 | O1 |
+---------+------------+----------------+----------+
| NULL | NULL | R2 | O2 |
+---------+------------+----------------+----------+
| NULL | NULL | NULL | O5 |
+---------+------------+----------------+----------+
| NULL | NULL | NULL | O6 |
+---------+------------+----------------+----------+
Uptill now i tried the following:
CREATE TABLE FACTS(
ObjectId NVARCHAR(255),
RentalObjectId NVARCHAR(255),
ContractId NVARCHAR(255),
PartyId NVARCHAR(255)
);
INSERT INTO FACTS(ContractId)
SELECT ContractId
FROM ContractTable
UDATE FACTS
SET FACTS.Party_id = PartyTable.PartyId
FROM FACTS FULL OUTER JOIN
PartyTable on PartyTable.PartyId = FACTS.PartyId;
Using that same update statement for the other field.
However this does not work.
I do not get the values on which the query does not find a match.
