sql

Question

How to use insert query using join in stored procedure

Khan Abrar Ahmed · Answer

Hi use this DECLARE @HDOC int exec sp_xml_preparedocument @HDOC OUTPUT,@ENTRYID INSERT INTO TRANS_PAYMENTINVOICE_DET ( ENTRYID , ENTRYNO , ENTRYORDERID , INVOICEENTRYID , PAYMODE , PAYDETAILS , PAYAMOUNT ) SELECT @ENTRYID , ENTRYNO , ENTRYORDERID , INVOICEENTRYID , PAYMODE , PAYDETAILS , PAYAMOUNT FROM OPENXML(@HDOC, '/ROOT/TRANS_PAYMENTINVOICE_DET',2) WITH (ENTRYID VARCHAR(30),ENTRYNO VARCHAR(15 ),ENTRYORDERID INT,INVOICEENTRYID VARCHAR(30),PAYMODE VARCHAR(30),PAYDETAILS VARCHAR(50),PAYAMOUNT NUMERIC(18,5)) AS PaymentINvoice INNER JOIN dbo.TRANS_INVOCE_DET AS aist ON PaymentInvoice.ENTRYID =aist.ENTRYID EXEC sp_xml_removedocument @hDoc SET @ERROR = @@ERROR; IF @ERROR 0 SET @ERR = @ERROR

snowal raj · Answer

i want join the TRANS_PAYMENTINVOICE_DET table to TRANS_INVOCE_DET TABLE COLUMN IS ENTRYID

Khan Abrar Ahmed · Answer

can u tell the table name and column join

snowal raj · Answer

i want used inner join

Khan Abrar Ahmed · Answer

Hi Please specify which join you want to use and where??

snowal raj · Answer

INSERT INTO TRANS_PAYMENTINVOICE_DET(ENTRYID,ENTRYNO,ENTRYORDERID,INVOICEENTRYID,PAYMODE,PAYDETAILS,PAYAMOUNT) SELECT @ENTRYID,ENTRYNO,ENTRYORDERID,INVOICEENTRYID,PAYMODE,PAYDETAILS,PAYAMOUNT FROM OPENXML (@HDOC, '/ROOT/TRANS_PAYMENTINVOICE_DET ',2) WITH (ENTRYID VARCHAR(30),ENTRYNO VARCHAR(15 ),ENTRYORDERID INT,INVOICEENTRYID VARCHAR(30),PAYMODE VARCHAR(30),PAYDETAILS VARCHAR(50),PAYAMOUNT NUMERIC(18,5)) SET @ERROR=@@ERROR;IF @ERROR 0 SET @ERR=@ERROR how to use join above the sp

Ranjit Powar · Answer

Give details of problem.

Khan Abrar Ahmed · Answer

Hi You can try this DECLARE @table AS TABLE (id int,vlaues VARCHAR(16)) DECLARE @table1 AS TABLE (id int,vlaues VARCHAR(16),idtext VARCHAR(16)) DECLARE @insertTable AS TABLE (id int,vlaues VARCHAR(16)) INSERT INTO @table ( id, vlaues ) VALUES ( 1, -- id - int 'A' -- vlaues - varchar(16) ), ( 2, -- id - int 'B' -- vlaues - varchar(16) ), ( 3, -- id - int 'C' -- vlaues - varchar(16) ) INSERT INTO @table1 ( id, vlaues, idtext ) VALUES ( 1, -- id - int 'AA', -- vlaues - varchar(16) 'A1' -- idtext - varchar(16) ), ( 2, -- id - int 'BB', -- vlaues - varchar(16) 'CC' -- idtext - varchar(16) ) INSERT INTO @insertTable SELECT t.id,t2.vlaues FROM @table AS t LEFT JOIN @table1 AS t2 ON t2.id = t.id SELECT * FROM @insertTable

sql

Answers (8)