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 INTINVOICEENTRYID VARCHAR(30)PAYMODE VARCHAR(30)PAYDETAILS VARCHAR(50)PAYAMOUNT NUMERIC(185)) 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(ENTRYIDENTRYNOENTRYORDERIDINVOICEENTRYIDPAYMODEPAYDETAILSPAYAMOUNT) SELECT @ENTRYIDENTRYNOENTRYORDERIDINVOICEENTRYIDPAYMODEPAYDETAILSPAYAMOUNT FROM OPENXML (@HDOC '/ROOT/TRANS_PAYMENTINVOICE_DET '2) WITH (ENTRYID VARCHAR(30)ENTRYNO VARCHAR(15 )ENTRYORDERID INTINVOICEENTRYID VARCHAR(30)PAYMODE VARCHAR(30)PAYDETAILS VARCHAR(50)PAYAMOUNT NUMERIC(185)) 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 intvlaues VARCHAR(16)) DECLARE @table1 AS TABLE (id intvlaues VARCHAR(16)idtext VARCHAR(16)) DECLARE @insertTable AS TABLE (id intvlaues 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.idt2.vlaues FROM @table AS t LEFT JOIN @table1 AS t2 ON t2.id = t.id SELECT * FROM @insertTable