locked
Need to insert transaction table without inserting in master table RRS feed

  • Question

  • Hi All,

    I have a scenerio where i need to insert values in transaction table without inserting in the master table.  Say i will be passing customer id through sp which is already in master table but customerid information i need to insert into transaction table.  I am getting error "The insert statment conflicted with the foreign key constraint "FK_....."  ".  Btw how i need to check the condition that these customerid is already exists in master table inorder to avert this error message.

     

    Thanks in advance

    R Anand

    Tuesday, July 26, 2011 1:04 PM

Answers

  • If i understand this based on the code [which looks different from what you originally described]
    1. You are trying to insert customer records.
    2. The conflicts happen around the categoryid which is in the parent table.

    So, while you are inserting, you can simply write

    INSERT INTO CUSTOMBERDETAILS(CustID,CATEGORYID,CREATEDATE,CREATEDBY,UPDATEDATE,UPDATEBY)
    SELECT CustID,CATEGORYID,CREATEDATE,CREATEDBY,UPDATEDATE,UPDATEBY FROM CUSTOMERDETAILS_MIGRATE
    WHERE EXISTS (SELECT CategoryID FROM Category WHERE Category.CategoryID = CUSTOMERDETAILS_MIGRATE.CategoryID)
    

    The exists cause simply checks to see if the category id in the customerdetails_migrate table are valid or not.

    • Proposed as answer by Surendra Nath GM Wednesday, July 27, 2011 2:25 PM
    • Marked as answer by KJian_ Tuesday, August 2, 2011 7:49 AM
    Wednesday, July 27, 2011 2:14 PM

All replies

  • Hello

    Welcome to the Transact SQL Forum.  Here are a few things to get you oriented.  First, give a look at these two guidelines for posting questions in the Transact SQL Forum:

    MSDN Transact SQL Forum Posting Guidelines:

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/af20783d-2489-46e4-b6f4-be0ab6d514cb

          Posted by Clifford Dibble

          http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ce8989b5-dc58-47d8-af65-db5bcd4f7ee2

          Posted by Phil Brammer

    Next, become aware of the code and samples that you post.  One thing that will help you get better answers for your questions is to make your source code more readable.  Therefore, this forum provides a code formatting tool.  You can find the code tool.  The code tool is identified on the tool pallet by an icon that looks like a tiny window with the symbols “</>”.  Use this tool to insert your sample code; this will help the people that read your code.

    If you are getting some kind of error condition, please provide a full description of the error condition; if you are getting an error message from SQL Server itself, please provide the exact error message is generated.

     


    Tuesday, July 26, 2011 1:08 PM
  • Hi Anand

     

    One quick solutions is to drop the FK constraint between master and transaction tables to do so.

    --> Btw how i need to check the condition that these customerid is already exists in master table inorder to avert this error message.

    You can use the below code

     

    IF EXIST ( SELECT 1 FROM <master_table> WHERE customer_id = <customer_id_to_be_inserted>)
    
     INSERT INTO <transaction_table> values (<values>)
    
    

     

     

     

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, July 26, 2011 1:26 PM
  • Hi

    Thanks for the reply.  Nope still i am getting the same message even though i specify IF EXISTS. 

     

    Thanks

    R Anand

    Tuesday, July 26, 2011 2:39 PM
  • If it does not work even after the EXISTS check, there may be an issue with the code. can you post the insert query and explain how the foreign key is defined?
    Tuesday, July 26, 2011 2:45 PM
  • Hi. Please find the below code for the Transaction Table.

    Create Table [dbo].[CustomerDetails](

    CustID int Not null,

    CategoryID int Not NULL,

    createDate datatime null,

    createdby int NUll,

    UpdateDate datetime Null,

    UpdatedBy INT NUll

    ) ON [PRIMARY]

     

    ALTER TABLE CustomerDetails WITH CHECK ADD CONSTRAINT [FK_CUSTDETAILS] FOREIGN KEY (CATEGORYID)

    REFERENCES [DBO].CATEGORYID (CATEGORYID)

    GO

    ALTER TABLE dbo.CustomerDetails CHECK CONSTRAINT [FK_CUSTDETAILS]

    GO

    So in the t-sql query my query would be like

    IF EXISTS (SELECT CATEGORYID FROM CATEGORY_MIGRATE WHERE CATEGORYID IN (SELECT CATEGORY FROM CATEGORY WHERE TYPEID = @desAppID))

    BEGIN

     

                INSERT INTO CUSTOMBERDETAILS(CustID,CATEGORYID,CREATEDATE,CREATEDBY,UPDATEDATE,UPDATEBY)

                SELECT CustID,CATEGORYID,CREATEDATE,CREATEDBY,UPDATEDATE,UPDATEBY FROM CUSTOMERDETAILS_MIGRATE)

    END

     

    Thanks in advance,

    R Anand

     

    Wednesday, July 27, 2011 8:26 AM
  • If i understand this based on the code [which looks different from what you originally described]
    1. You are trying to insert customer records.
    2. The conflicts happen around the categoryid which is in the parent table.

    So, while you are inserting, you can simply write

    INSERT INTO CUSTOMBERDETAILS(CustID,CATEGORYID,CREATEDATE,CREATEDBY,UPDATEDATE,UPDATEBY)
    SELECT CustID,CATEGORYID,CREATEDATE,CREATEDBY,UPDATEDATE,UPDATEBY FROM CUSTOMERDETAILS_MIGRATE
    WHERE EXISTS (SELECT CategoryID FROM Category WHERE Category.CategoryID = CUSTOMERDETAILS_MIGRATE.CategoryID)
    

    The exists cause simply checks to see if the category id in the customerdetails_migrate table are valid or not.

    • Proposed as answer by Surendra Nath GM Wednesday, July 27, 2011 2:25 PM
    • Marked as answer by KJian_ Tuesday, August 2, 2011 7:49 AM
    Wednesday, July 27, 2011 2:14 PM