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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 The INSERT statement conflicted with the FOREIGN K
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

4 Posts

Posted - 09/29/2013 :  17:40:36  Show Profile  Reply with Quote
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
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

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 09/30/2013 :  08:51:41  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000