Solved

Using a reference table to validate data on insert

Posted on 2015-02-19
4
52 Views
Last Modified: 2015-02-23
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
0
Comment
Question by:Morpheus7
  • 2
4 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40618962
You can use a FOREIGN KEY constraint. That will prevent any inserts from not being in the Reference table.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40618970
>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.

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

Open in new window


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, ...
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40619122
Add a Foreign Key to prevent the insert:

ALTER TABLE dbo.target_table
    ADD CONSTRAINT target_table__FK_ProdCode
    FOREIGN KEY ( ProdCode ) REFERENCES dbo.reference_table ( ProdCode )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40619400
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now