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.
Loading
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.
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Piyush PansuriyaPosted Feb 20, 2018, 4:55 AM
@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