KavyaVS
asked on
How to insert Data into Two Tables in single Storedprocedure where primarykey in one Table is Foreignkey in another Table
Hi,
I am inserting data into two Tables in one stored procedure.TableA the primary key is ProductID.It is auto incrementing.It is foreing key in TableB.
TableB the primarykey is ProductId and ProductDetailsID.ProductDe taildID is auto incrementing.I need to enter the max(productId) from TableA.
Can we do this in Single procedure.
How to do that.
CREATE PROCEDURE [dbo].[usp_InsertProduct]
(
@Product_Desc varchar(100),
@ProductName varchar(100),
@ProductDetails varchar(200),
@ProductPrice varchar(200),
@StarDate datetime,
@EndDate datetime,
@AddedBy varchar(100),
@AddedOn datetime,
@ProductType varchar(100)
)
As
Begin
Insert Into [dbo].[t_TableA]
(
Product_Desc,
ProductPrice,
StartDate,
EndDate,
AddedBy,
AddedOn
)
Values
(
@Product_Desc,
@ProductPrice,
@StartDate,
@EndDate,
@AddedBy,
@AddedOn
);
INSERT INTO [dbo].[t_TableB]
(
ProductName,
ProductDetails,
StartDate,
EndDate ,
AddedBy,
AddedOn
)
VALUES
(
@ProductName,
@ProductDetails,
@StartDate,
@EndDate,
@AddedBy,
@AddedOn
)
End
I am inserting data into two Tables in one stored procedure.TableA the primary key is ProductID.It is auto incrementing.It is foreing key in TableB.
TableB the primarykey is ProductId and ProductDetailsID.ProductDe
Can we do this in Single procedure.
How to do that.
CREATE PROCEDURE [dbo].[usp_InsertProduct]
(
@Product_Desc varchar(100),
@ProductName varchar(100),
@ProductDetails varchar(200),
@ProductPrice varchar(200),
@StarDate datetime,
@EndDate datetime,
@AddedBy varchar(100),
@AddedOn datetime,
@ProductType varchar(100)
)
As
Begin
Insert Into [dbo].[t_TableA]
(
Product_Desc,
ProductPrice,
StartDate,
EndDate,
AddedBy,
AddedOn
)
Values
(
@Product_Desc,
@ProductPrice,
@StartDate,
@EndDate,
@AddedBy,
@AddedOn
);
INSERT INTO [dbo].[t_TableB]
(
ProductName,
ProductDetails,
StartDate,
EndDate ,
AddedBy,
AddedOn
)
VALUES
(
@ProductName,
@ProductDetails,
@StartDate,
@EndDate,
@AddedBy,
@AddedOn
)
End
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
But in the original q you stated:
>> TableA the primary key is ProductID.It is auto incrementing. <<
>> TableA the primary key is ProductID.It is auto incrementing. <<
ASKER
I thought it was.But not.It's giving the error.
Thanks
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried as it is you specified. I am getting this error.
Cannot insert the value NULL into column 'ProductID', table 'dbo.t_TableB'; column does not allow nulls. INSERT fails.
Please let me know.
Thanks
Cannot insert the value NULL into column 'ProductID', table 'dbo.t_TableB'; column does not allow nulls. INSERT fails.
Please let me know.
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am getting the error here.
Incorrect syntax near the keyword 'FROM'.
SET @ProductId = (SELECT TOP (1) FROM @ProductIds);
Thanks.
Incorrect syntax near the keyword 'FROM'.
SET @ProductId = (SELECT TOP (1) FROM @ProductIds);
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
ASKER
Cannot insert the value NULL into column 'ProductID', table 'dbo.t_TableA'; column does not allow nulls. INSERT fails.
ProductID is not automatically incrementing.We need to set them explicitly.
Thanks.