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