locked
Foreign Key Constraint Insert RRS feed

  • Question

  • Hi,

    I am importing from a CSV file into a temporary table (TableA), then trying to import all values from the temporary table into a table has some foreign key constraints (TableB).

    Is there a way to loop through TableA and move all records that will have a foreign key constrains into a third table (TableC)? TableC will be a place holder table for all broken records that I will deal with manually

    Thanks

    Thursday, December 5, 2019 1:34 PM

Answers

All replies

  • >>>that will have a foreign key constrains

    Can you elaborate on above? What does it mean?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 5, 2019 1:39 PM
    Answerer
  • let us say TableB has the following columns:

    OrderId,
    Description,
    Status,   <- FK to ScheduleStatus Table
    ProductName  <--FK to a Product table

    ScheduleStatus table has the following values:

    StatusId, Name

    1          , New
    2          , Exists
    3...etc

    If in my INSERT statement I try to insert 0 in my TableB then it blow on FK constraints violation

    Thursday, December 5, 2019 1:46 PM
  • INSERT INTO TableC SELECT * FROM TableB WHERE NOT  EXISTS (SELECT * FROM Statuses S WHERE S.StatusID=TableB.Status)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, December 5, 2019 1:50 PM
    Answerer
  • Thank you Uri,

    can the record be deleted from TableB after being inserted into TableC or do I have to have another query for that?

    at the end, I want all records in TableB to be valid so I can import them successfully

    Thanks

    Thursday, December 5, 2019 1:55 PM
  • Yes

    DELETE FROM TableB WHERE NOT  EXISTS (SELECT * FROM Statuses S WHERE S.StatusID=TableB.Status)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, December 5, 2019 1:56 PM
    Answerer
  • Great. Thank you for the prompt reply
    Thursday, December 5, 2019 1:57 PM
  • >> let us say TableB has the following columns: <<

    Why did you fail to post DDL? Why didn't you know how to name a table or a  column? There is no such thing as a magic universal "status"; is it marital? Employment? Graduation? Let's try and turn your vague narrative into a real table.

    CREATE TABLE Orders
    (order_id CHAR(10) NOT NULL PRIMARY KEY,
     order_description VARCHAR(50) NOT NULL,
     order_status CHAR(5) NOT NULL
         CHECK (order_status IN (...)),   
     product_name CHAR(25)  NOT NULL
        REFERENCES Products(product_name)
    );

    Did you know that a table must have a key by definition? 

    Since the order status will be a relatively small set of constant values, we can use a check constraint. Presumably, the products that we sell the larger set and its dynamic, so we can use a reference to a lookup table. 

    Your narrative descriptions tell us that you don't think relational yet. Your approach is basically what would be done with punchcards. If you'd like to try again, then post some real DDL instead of narratives and will see what we can do.

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, December 5, 2019 8:15 PM