storing only single value in primary key table and storing multiple values in foreign key table
-
CREATE PROCEDURE .InsertTwoTables @UserName nvarchar(50), @ShopName nvarchar(50), @Email nvarchar(300) , @time1 datetime, @CategoryNameE nvarchar(50), @ItemKey nvarchar(50), @ItemKeyNameE nvarchar(50), @CurrentQty nvarchar(50), @SalesPrice nvarchar(50), @Quantity nvarchar(50), @Total nvarchar(50) AS BEGIN TRANSACTION Set Nocount On DECLARE @OrderID int //primary key table INSERT INTO TblOrder(UserName, ShopName, Email,time1) VALUES (@UserName,@ShopName, @Email,@time1) SELECT @OrderID=@@IDENTITY //foreign key table INSERT INTO TblDetails (OrderNo, CategoryNameE , ItemKey ,ItemKeyNameE , CurrentQty, SalesPrice, Quantity , total) VALUES (@OrderID, @CategoryNameE , @ItemKey, @ItemKeyNameE, @CurrentQty, @SalesPrice, @Quantity , @Total) COMMIT when iam inserting values with these procedure it is saving ordersno also repeated i want one orderid with multiple values in TblDetails can you give me example which helps me
-
CREATE PROCEDURE .InsertTwoTables @UserName nvarchar(50), @ShopName nvarchar(50), @Email nvarchar(300) , @time1 datetime, @CategoryNameE nvarchar(50), @ItemKey nvarchar(50), @ItemKeyNameE nvarchar(50), @CurrentQty nvarchar(50), @SalesPrice nvarchar(50), @Quantity nvarchar(50), @Total nvarchar(50) AS BEGIN TRANSACTION Set Nocount On DECLARE @OrderID int //primary key table INSERT INTO TblOrder(UserName, ShopName, Email,time1) VALUES (@UserName,@ShopName, @Email,@time1) SELECT @OrderID=@@IDENTITY //foreign key table INSERT INTO TblDetails (OrderNo, CategoryNameE , ItemKey ,ItemKeyNameE , CurrentQty, SalesPrice, Quantity , total) VALUES (@OrderID, @CategoryNameE , @ItemKey, @ItemKeyNameE, @CurrentQty, @SalesPrice, @Quantity , @Total) COMMIT when iam inserting values with these procedure it is saving ordersno also repeated i want one orderid with multiple values in TblDetails can you give me example which helps me
The way the stored procedure is currently there will only be a one to one relationship. I would suggest removing the time from TblOrder then before an insert check to see if the values UserName, ShopName, Email already exist. Something like (not tested)
DECLARE @there INT
SELECT * FROM TblOrder
WHERE UserName = @username
AND ShopName = @shopname
AND Email = @email
SET @there = @@rowcount
IF @there = 0
BEGIN
-- Your insert
ENDHope this helps djj
-
CREATE PROCEDURE .InsertTwoTables @UserName nvarchar(50), @ShopName nvarchar(50), @Email nvarchar(300) , @time1 datetime, @CategoryNameE nvarchar(50), @ItemKey nvarchar(50), @ItemKeyNameE nvarchar(50), @CurrentQty nvarchar(50), @SalesPrice nvarchar(50), @Quantity nvarchar(50), @Total nvarchar(50) AS BEGIN TRANSACTION Set Nocount On DECLARE @OrderID int //primary key table INSERT INTO TblOrder(UserName, ShopName, Email,time1) VALUES (@UserName,@ShopName, @Email,@time1) SELECT @OrderID=@@IDENTITY //foreign key table INSERT INTO TblDetails (OrderNo, CategoryNameE , ItemKey ,ItemKeyNameE , CurrentQty, SalesPrice, Quantity , total) VALUES (@OrderID, @CategoryNameE , @ItemKey, @ItemKeyNameE, @CurrentQty, @SalesPrice, @Quantity , @Total) COMMIT when iam inserting values with these procedure it is saving ordersno also repeated i want one orderid with multiple values in TblDetails can you give me example which helps me