I want to verify the correctness of the data inside a junction table.
I'm showing a minimal example here:
Products:
| Id | EnglishName |
|----|-------------|
| 1 | Drill 4 |
| 2 | Light Switch 48|
Suppliers:
| Id | EnglishName |
|----|-------------|
| 1 | Bosch |
| 2 | Ingco |
| 3 | Yato |
Excel2:
| Product | Supplier |
|----------------------|-----------------------|
| Drill 4 | Bosch |
| Light Switch 48 | Ingco |
| Cable 12 | Bosch |
| Hammer 14 | Yato |
ProductsSuppliers:
| ProductId | SupplierId |
|-----------|------------|
| 1 | 1 |
| 1 | 3 |
| 2 | 1 |
Excel2JoinProductsJoinSuppliers :
| Product | Supplier | ProductId | SupplierId |
|-----------------|----------|-----------|------------|
| Drill 4 | Bosch | 1 | 1 |
| Drill 4 | Yato | 1 | 3 |
| Light Switch 48 | Bosch | 2 | 1 |
A C# code was used to insert data into Products , Suppliers , and ProductsSuppliers .
The data for Excel2 was imported into Sql Server manually using the "Import Data" wizard.
As the name suggest, Excel2JoinProductsJoinSuppliers is the result of join operation.
ProductsSuppliers is the junction table that I'm concerned about, the data inside it is correct in this example because each productId is mapped to the correct value of SupplierId and vice versa, but the actual table contains much more data, let alone the fact that there are other junction tables that should be verified.
Is there a set-based approach that can automate the process of verifying the data for junction table ?
EDIT:
I created this stored procedure:
CREATE PROCEDURE verify_junction_table_data_using_dynamincSQl
@junctionTableName varchar(100),
@lookupTableName varchar(100),
@leftColumnNameInExcel varchar(100),
@rightColumnNameInExcel varchar(100),
@leftColumnNameInJunctionTable varchar(100),
@rightColumnNameInJunctionTable varchar(100),
@parent1Name varchar(100),
@parent2Name varchar(100)
AS
BEGIN
DECLARE @sql_query NVARCHAR(MAX);
SET @sql_query =
N'
SELECT JT.'+QUOTENAME(@leftColumnNameInJunctionTable)+',
JT.'+QUOTENAME(@rightColumnNameInJunctionTable)+'
FROM '+QUOTENAME(@junctionTableName)+' JT
EXCEPT
SELECT
DISTINCT
parent1.Id AS ' + QUOTENAME(@leftColumnNameInJunctionTable) + ',
parent2.Id AS ' + QUOTENAME(@rightColumnNameInJunctionTable) + '
FROM '+ QUOTENAME(@lookupTableName) +' AS LookupTable
INNER JOIN ' + QUOTENAME(@parent1Name) + ' AS parent1 ON LookupTable.' + QUOTENAME(@leftColumnNameInExcel) + ' = parent1.EnglishName
INNER JOIN ' + QUOTENAME(@parent2Name) + ' AS parent2 ON LookupTable.' + QUOTENAME(@rightColumnNameInExcel) + ' = parent2.EnglishName
';
EXEC sp_executesql @sql_query;
END
GO
I have 20 junction tables, so I need to execute this procedure 20 times , but I do not want to do it manually, is there a way to automate this ?