How to Insert Multiple salesOrders for a single sale

See more:

SQL-Server-2008R2

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??