Conflict with multiple insert in two tables

Question

I am trying to insert all the rows of cart Table of userId=1 into Order and OrderItem table the Order table consists of following columns:

ORDER TABLE

1.OrderId (int)

2.OrderDate(datetime)

3.userId (int)

4.orderStatus (varchar)

the user_tbltable is reference to the Order table with PK of userId and cart table is also reference with userId

CART TABLE

Id

userId

Quantity

productID

ORDERITEM TABLE

id

orderid

productid

qty

ITEM Table

ProductId

Name

SizeId

ColorId

Price

Imageurl

I have tried following but this giving me conflict error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_OrderItem_Item". The conflict occurred in database "db", table "dbo.Item", column 'Id'

insert into [Order] values(getdate(),@userId,@status);
set @orderId = @@IDENTITY
insert into OrderItem (OrderId ,ItemID,Qty,ColorId,SizeId)
select @orderId,Cart.ProductId,cart.Quantity,cart.ColorId,cart.SizeId 
from [Cart] 
where UserId=@userId ;

I have no idea why this received an upvote. Without some details this is guess work. Which of your two inserts causes the error? Probably the second one? Can you share the ddl of your table AND the foreign key? My guess is you are trying to put something in OrderItem that doesn't exist in your Item table (but that table isn't in your question). — Sean Lange
I checked the item table the record is not exists that is causing the error ----Thanks — Ravi Patil
Glad you figured it out. Next time please post actual ddl (create table statements) instead of a list of columns. It would make your questions a lot easier to help with. — Sean Lange

Jayasurya Satheesh Jayasurya Satheesh · Accepted Answer · 2018-02-27T12:50:11

Change your Query as Below

INSERT INTO [Order]
VALUES
(
    GETDATE(),
    @userId,
    @status
);

SELECT @orderId = SCOPE_IDENTITY();--Use SCOPE_IDENTITY Instead of @@IDENTITY

INSERT INTO OrderItem
(
    OrderId,
    ItemID,
    Qty,
    ColorId,
    SizeId
)
       SELECT [Order].OrderId,
              Cart.ProductId,
              cart.Quantity,
              cart.ColorId,
              cart.SizeId
       FROM [Cart] 
       INNER JOIN [Order]
       ON [Cart].UserId = @userId
          AND [Order].OrderId = @orderId;--SELECT Order Id From Order to be more safer

Conflict with multiple insert in two tables

ORDER TABLE

1 Answers