Morpheus7
asked on
Using a reference table to validate data on insert
Hi,
Is it possible to validate a specific column on insert from one table to another using a reference table?.
Reference Table is as follows:
LineID INT ProdCode VARCHAR (10)
Source table and target table both have column ProdCode VARCHAR (10)
If the ProdCode does not exist in the Reference table, then the insert to the target table should fail.
Any help would be appreciated.
Thanks
Is it possible to validate a specific column on insert from one table to another using a reference table?.
Reference Table is as follows:
LineID INT ProdCode VARCHAR (10)
Source table and target table both have column ProdCode VARCHAR (10)
If the ProdCode does not exist in the Reference table, then the insert to the target table should fail.
Any help would be appreciated.
Thanks
You can use a FOREIGN KEY constraint. That will prevent any inserts from not being in the Reference table.
>If the ProdCode does not exist in the Reference table, then the insert to the target table should fail
A JOIN would do that, as it would limit the set to be inserted to only those ProdCode values that exist in both source_table and list_of_prod_codes.
Then you'll have to ask yourself how to handle the rows where there is no matching ProdCode, such as do nothing, write another query and send the results to somebody, create a 'dummy' record in list_of_prod_codes, ...
A JOIN would do that, as it would limit the set to be inserted to only those ProdCode values that exist in both source_table and list_of_prod_codes.
INSERT INTO target_table (ProdCode, goo, foo, boo)
SELECT s.ProdCode s.goo, s.foo, s.boo
FROM source_table s
JOIN list_of_prod_codes pc ON s.ProdCode = pc.ProdCode
Then you'll have to ask yourself how to handle the rows where there is no matching ProdCode, such as do nothing, write another query and send the results to somebody, create a 'dummy' record in list_of_prod_codes, ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
btw if you're using SSIS to perform this insert, then you can pull off the 'validation' with a data pump that has these components:
Data Source, whatever source_table is
Lookup: Queries the list_of_prod_codes table, where you can join the two tables based on ProdCode. This will result in two paths, Lookup Match and Lookup No match.
Data Destination: Connect the 'Lookup Match' to your destination.
Then you can decide how you want to handle the 'Lookup No Match' path.