Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 The INSERT statement conflicted with the FOREIGN K

Author  Topic 

arv_rehal1381
Starting Member

4 Posts

Posted - 2013-09-29 : 17:40:36
I need to copy information from few tables to one table. I am inserting values to "SalesOrder" table but there is foreign key column "OpportunityId" which is primary column in Opportunity table, but when I am inserting value using my view it is giving me referential error.

Here is my view..

SELECT dbo.ToGuid(dbo.OrdMain.Order_ID) AS SalesOrderId, dbo.ToGuid(dbo.OrdMain.Opportunity_ID) AS OpportunityId, 1 AS PriorityCode,
dbo.ToGuid(N'592A6ECEEEF6E211BC40005056B10074') AS OwningBusinessUnit, dbo.ToGuid(N'10A6EA56D7A5E211BA9E005056B10074') AS PriceLevelId,
dbo.OrdMain.Order_Num AS OrderNumber, dbo.OrdMain.Summary AS Description, CONVERT(MONEY, dbo.OrdMain.OrderDiscount_natural) AS DiscountAmount,
CONVERT(MONEY, dbo.OrdMain.ListTotal_natural) AS TotalAmount, CONVERT(MONEY, dbo.OrdMain.ItemPriceTotal_natural) AS TotalLineItemAmount,
CONVERT(MONEY, dbo.OrdMain.ItemDiscTotal_natural) AS TotalLineItemDiscountAmount, CONVERT(MONEY, dbo.OrdMain.ShipHandle_natural) AS FreightAmount,
CONVERT(MONEY, dbo.OrdMain.DiscountTotal_natural) AS TotalDiscountAmount, dbo.OrdMain.ShipRequestDate AS RequestDeliveryBy, CONVERT(MONEY,
dbo.OrdMain.TaxTotal_natural) AS TotalTax, dbo.OrdMain.PayTerms AS PaymentTermCode, CONVERT(INT, dbo.OrdMain.DeliveryCode) AS ShippingMethodCode,
dbo.SystemUserBase.SystemUserId AS CreatedBy, dbo.OrdMain.AddDate AS CreatedOn, SystemUserBase_1.SystemUserId AS ModifiedBy,
dbo.OrdMain.ChangeDate AS ModifiedOn, 0 AS StateCode, dbo.OrdMain.Address1 AS ShipTo_Line1, dbo.OrdMain.Address2 AS ShipTo_Line2,
dbo.OrdMain.Address3 AS ShipTo_Line3, dbo.OrdMain.City AS ShipTo_City, dbo.OrdMain.State AS ShipTo_StateorProvince, dbo.OrdMain.Zip AS ShipTo_PostalCode,
dbo.OrdMain.Country AS ShipTo_Country, dbo.OrdMain.PriPhone AS ShipTo_Telephone, dbo.OrdMain.BillTo AS BillTo_ContactName,
dbo.OrdMain.BillAddr1 AS BillTo_Line1, dbo.OrdMain.BillAddr2 AS BillTo_Line2, dbo.OrdMain.BillAddr3 AS BillTo_Line3, dbo.OrdMain.BillCity AS BillTo_City,
dbo.OrdMain.BillState AS BillTo_StateOrProvince, dbo.OrdMain.BillZip AS BillTo_PostalCode, dbo.OrdMain.BillCountry AS BillTo_Country,
dbo.Org1.OrgName AS BillTo_Name, dbo.TransactionCurrencyBase.TransactionCurrencyId, dbo.TransactionCurrencyBase.ExchangeRate, CONVERT(MONEY,
dbo.OrdMain.ListTotal_natural) AS TotalLineItemAmount_Base, CONVERT(MONEY, dbo.OrdMain.DiscountTotal_natural) AS TotalDiscountAmount_Base,
CONVERT(MONEY, dbo.OrdMain.SubTotal_natural) AS TotalamountLessFreight_Base, CONVERT(MONEY, dbo.OrdMain.GrandTotal_natural) AS TotalAmount_Base,
CONVERT(MONEY, dbo.OrdMain.ItemDiscTotal_natural) AS DiscountAmount_Base, CONVERT(MONEY, dbo.OrdMain.ItemDiscTotal_natural)
AS TotalLineItemDiscountAmount_Base, CONVERT(MONEY, dbo.OrdMain.TaxTotal_natural) AS TotalTax_Base, dbo.ToGuid(dbo.OrdMain.Org_ID) AS CustomerId,
1 AS CustomerIdType, 8 AS OwnerIdType, dbo.Org1.OrgName AS CustomerIdName, dbo.OrdMain.OrderStat AS Status
FROM dbo.OrdMain LEFT OUTER JOIN
dbo.Person ON dbo.OrdMain.Per_ID = dbo.Person.Per_ID LEFT OUTER JOIN
dbo.TransactionCurrencyBase ON dbo.OrdMain.CurrencyCode = dbo.TransactionCurrencyBase.ISOCurrencyCode LEFT OUTER JOIN
dbo.SystemUserBase AS SystemUserBase_1 ON dbo.OrdMain.ChangeUser = SystemUserBase_1.UserName LEFT OUTER JOIN
dbo.Org1 ON dbo.OrdMain.Org_ID = dbo.Org1.Org_ID LEFT OUTER JOIN
dbo.SystemUserBase ON dbo.OrdMain.AddUser = dbo.SystemUserBase.UserName



Thanks in advance for any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-30 : 08:51:41
that means the value you're trying to insert is not a valid value existing in Oppurtunity table. Make sure you check and insert missing values first in Parent table before do insertion on SalesOrder

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -