How to insert data into two related tables?

Question

This is my data. I want to insert my data into 2 table product and parameter.first product data will insert ,and generated id will insert into parameter table.

here i am attaching my procedure and current esult and expected result.

Piyush Pansuriya · Answer

Hi, Alter PROCEDURE xmlOrderInsert( @order nvarchar(max) ) AS DECLARE @index int, @OID int EXEC sp_xml_preparedocument @index OUTPUT, @order BEGIN TRANSACTION insert into Tbl_ProductMaster (ProductName, ProductDescription, ClientID , IsActive , Fromdate, Todate ) select PProductName,PDescription,PClientID,'True' as PisActive ,PFromdate,PTodate FROM OPENXML (@index, '/ArrayOfProduct/Product',2) WITH ( PProductName varchar(50), PDescription varchar(6), PClientID varchar(30), PFromdate datetime, PTodate datetime ) IF @@ERROR 0 BEGIN ROLLBACK TRANSACTION RETURN -100 END SET @OID = IDENT_CURRENT(' Tbl_ProductMaster ') INSERT INTO [Tbl_ParameterTransact] ( ProdID, ClientID, ParameterID) --(2,1,1,@OID) SELECT @OID AS ProductID,PClientID, PParameterID FROM OpenXml( @index, '/ArrayOfProduct/Product/GetSetParameter/Parameter',2) WITH ( PClientID int, PParameterID int ) IF @@ERROR 0 BEGIN ROLLBACK TRANSACTION RETURN -101 END COMMIT TRANSACTION EXEC sp_xml_removedocument @index SELECT @OID AS ProductID

How to insert data into two related tables?

Answers (1)