如何为单个销售插入多个salesOrders [英] How to Insert Multiple salesOrders for a single sale

问题描述

您好我正在为单个销售详细信息插入多个销售订单。

我创建的表是



父表

Hi I am inserting mutiple sales orders for a single sales details.
The tables that I have created are

Parent Table

CREATE TABLE Tbl_Sales(SalesID INT CONSTRAINT pk_Tbl_Sales_SalesID PRIMARY KEY IDENTITY(101,1),CustomerID INT CONSTRAINT FK_Tbl_Customers_CustomerID FOREIGN KEY (CustomerID) REFERENCES Tbl_Customers(CustomerID),DateOfSale DATE DEFAULT GETDATE(),SalesName VARCHAR(50) CONSTRAINT Un_SalesName UNIQUE)

儿童表

Child Table

CREATE TABLE Tbl_SalesDetails(SalesDetailID INT CONSTRAINT pk_SalesID PRIMARY KEY IDENTITY(1,1),ModelID int constraint fk_SalModelID references Tbl_Models(ModelID),CustomerID int Constraint fk_CustomerID references Tbl_Customer(CustomerID),Quantity int,Unit varchar(20) default 'Pcs',Price decimal(18,2),Amount decimal(18,2),ForwardQuantity int,ForwardPrice decimal(18,2),AmountPaid decimal(18,2),DateOfSale date default getdate())

和临时表

And The Temp Table

CREATE TABLE Tbl_SalesItemDetails(SalesItemID INT CONSTRAINT pk_SalesItemID PRIMARY KEY IDENTITY(1,1),ModelID int constraint fk_ItemModelID references Tbl_Products(ModelID),CustomerID int Constraint fk_SalesCustomerID references Tbl_Customers(CustomerID),Quantity int,Unit varchar(20) default 'Pcs',Price decimal(18,2),Amount decimal(18,2),ForwardQuantity int,ForwardPrice decimal(18,2),AmountPaid decimal(18,2),TotalAmount DECIMAL(18,2),DateOfSale date default getdate(),SalesName VARCHAR(50))

现在我已经编写了存储过程来插入数据

Now I have written store procedure to insert the data

ALTER PROCEDURE Sp_CommitSales
AS
BEGIN
DECLARE @SalesID INT

BEGIN TRAN
    INSERT INTO Tbl_Sales (SalesName,CustomerID, TotalAmount)
    SELECT  top 1 SalesName,CustomerID,TotalAmount FROM Tbl_SalesItemDetails  order by SalesItemID DESC

    set @SalesID=SCOPE_IDENTITY();

    INSERT INTO Tbl_SalesDetails(ModelID,CustomerID,Quantity,Unit,Price,Amount,ForwardQuantity,ForwardPrice,TotalAmount,DateOfSale,SalesID)
    SELECT ModelID,CustomerID,Quantity,Unit,Price,Amount,ForwardQuantity,ForwardPrice,TotalAmount,DateOfSale,@SalesID
    FROM Tbl_SalesItemDetails

COMMIT TRAN
END

当我第一次将记录插入临时表记录插入但如果我执行存储过程多个记录插入我错误的地方请建议我??

When I first insert records into temp table records inserting but if I execute store procedure multiple records are inserting where i am doing wrong please suggest me??

推荐答案

我想，你必须使用IDENT_CURRENT而不是SCOPE_IDENTITY。

其中一篇文章中提供了相同的详细信息：

@@ IDENTITY，SCOPE_IDENTITY，IDENT_CURRENT之间的区别 [ ^ ]



谢谢，

Baliram Suryawanshi

I guess, you have to use IDENT_CURRENT instead of SCOPE_IDENTITY.
Details of the same are available in one of the article :
Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT[^]

Thanks,
Baliram Suryawanshi

这篇关于如何为单个销售插入多个salesOrders的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！