Inserting multiple lines into a table using declare and result from a select

Question

I am trying to create a SQL statement to insert multiple lines into a table based on the result of a select statement.

The purpose of this is we have 2 tables that should contain lines of data relating to the same data. I have created a statement to identify all the lines in one table that are not in another and a second statement to insert those lines into the 2nd table.

Both of these work individually the problem I am having is when I try combine them hoping to run all the insert in one go it succeeds but only on the first line of results

IF OBJECT_ID('tempdb..#retentiontemp') IS NOT NULL
   /* Then it exists */
   DROP TABLE #retentiontemp

Select * 
Into #retentiontemp
From     
     (SELECT VCHRNMBR,dex_row_id
      FROM JCRFP100
      WHERE VCHRNMBR NOT IN (SELECT VCHRNMBR FROM JC10500)
     ) As VCHRNMBR

declare @VENDORID char(15),@VCHRNMBR char(20), @DOCTYPE smallint, @BACHNUMB char(15), @ACTINDX int, @TAXDTLID char(15), @TAXAMNT numeric(19,5), @ORTAXAMT numeric(19,5), @PCTAXAMT numeric(19,5), @ORPURTAX numeric(19,5), @TDTTXPUR numeric(19,5), @ORTXBPUR int, @TXDTTPUR numeric(19,5), @ORTOTPUR numeric(19,5), @POSTED tinyint;

select

@VENDORID = VENDORID,
@VCHRNMBR = VCHNUMWK,
@DOCTYPE = DOCTYPE,
@BACHNUMB = BACHNUMB,   
@TAXDTLID = Taxschid,   
@TAXAMNT = TAXAMNT,
@TDTTXPUR = CURTRXAM,   
@ORTXBPUR = CURTRXAM,       
@TXDTTPUR = CURTRXAM,       
@ORTOTPUR = CURTRXAM,   
@ORTAXAMT = @TAXAMNT,   
@PCTAXAMT = @TAXAMNT,   
@ORPURTAX = @TAXAMNT,  
@POSTED = POSTED

from jcrfp100 where dex_row_id in (select dex_row_id from #retentiontemp)

If @TAXAMNT Is Null 

set @ACTINDX = '27'
Else
 set @ACTINDX = '28'

INSERT INTO PM10500 (VENDORID, VCHRNMBR, DOCTYPE, BACHNUMB, TAXDTLID, BKOUTTAX, TAXAMNT, ORTAXAMT, PCTAXAMT, ORPURTAX, FRTTXAMT, ORFRTTAX, MSCTXAMT, ORMSCTAX, ACTINDX, TRXSORCE, TDTTXPUR, ORTXBPUR, TXDTTPUR, ORTOTPUR, CURRNIDX, POSTED) VALUES ( @VENDORID, @VCHRNMBR, @DOCTYPE, @BACHNUMB, @TAXDTLID, '0', @TAXAMNT, @ORTAXAMT, @PCTAXAMT, @ORPURTAX, '0', '0', '0', '0', @ACTINDX, '0', @TDTTXPUR, @ORTXBPUR, @TXDTTPUR, @ORTOTPUR, '0', @POSTED)

INSERT INTO JC10500 (VENDORID, VCHRNMBR, DOCTYPE, BACHNUMB, TAXDTLID, BKOUTTAX, TAXAMNT, ORTAXAMT, PCTAXAMT, ORPURTAX, FRTTXAMT, ORFRTTAX, MSCTXAMT, ORMSCTAX, ACTINDX, TRXSORCE, TDTTXPUR, ORTXBPUR, TXDTTPUR, ORTOTPUR, CURRNIDX, POSTED) VALUES ( @VENDORID, @VCHRNMBR, @DOCTYPE, @BACHNUMB, @TAXDTLID, '0', @TAXAMNT, @ORTAXAMT, @PCTAXAMT, @ORPURTAX, '0', '0', '0', '0', @ACTINDX, '0', @TDTTXPUR, @ORTXBPUR, @TXDTTPUR, @ORTOTPUR, '0', @POSTED)

select * from PM10500 where VCHRNMBR in (select VCHRNMBR from #retentiontemp)
select * from JC10500 where VCHRNMBR in (select VCHRNMBR from #retentiontemp)

Possible duplicate of [SQL Server SELECT into existing table](http://stackoverflow.com/questions/4101739/sql-server-select-into-existing-table) — Tab Alleman, Mar 04 '16 at 14:45

score 0 · Answer 1 · answered Mar 04 '16 at 11:05

Variables can only store one value at a time, so when you use your INSERT INTO - VALUES statements, only one line will be entered.

Try INSERT INTO - SELECT instead. Something like the following:

IF OBJECT_ID('tempdb..#retentiontemp') IS NOT NULL
   /* Then it exists */
   DROP TABLE #retentiontemp

Select * 
Into #retentiontemp
From     
     (SELECT VCHRNMBR,dex_row_id
      FROM JCRFP100
      WHERE VCHRNMBR NOT IN (SELECT VCHRNMBR FROM JC10500)
     ) As VCHRNMBR


INSERT INTO PM10500 (VENDORID, VCHRNMBR, DOCTYPE, BACHNUMB, TAXDTLID, BKOUTTAX, TAXAMNT, ORTAXAMT, PCTAXAMT, ORPURTAX, FRTTXAMT, ORFRTTAX, MSCTXAMT, ORMSCTAX, ACTINDX, TRXSORCE, TDTTXPUR, ORTXBPUR, TXDTTPUR, ORTOTPUR, CURRNIDX, POSTED) 
SELECT
VENDORID, VCHNUMWK, DOCTYPE, BACHNUMB, Taxschid, '0', TAXAMNT, TAXAMNT, TAXAMNT, TAXAMNT, '0', '0', '0', '0', CASE WHEN TAXAMNT IS NULL THEN '27' ELSE '28' END, '0', CURTRXAM, CURTRXAM, CURTRXAM, CURTRXAM, '0' POSTED FROM jcrfp100 WHERE dex_row_id in (SELECT dex_row_id FROM #retentiontemp)

INSERT INTO JC10500 (VENDORID, VCHRNMBR, DOCTYPE, BACHNUMB, TAXDTLID, BKOUTTAX, TAXAMNT, ORTAXAMT, PCTAXAMT, ORPURTAX, FRTTXAMT, ORFRTTAX, MSCTXAMT, ORMSCTAX, ACTINDX, TRXSORCE, TDTTXPUR, ORTXBPUR, TXDTTPUR, ORTOTPUR, CURRNIDX, POSTED) 
SELECT
VENDORID, VCHNUMWK, DOCTYPE, BACHNUMB, Taxschid, '0', TAXAMNT, TAXAMNT, TAXAMNT, TAXAMNT, '0', '0', '0', '0', CASE WHEN TAXAMNT IS NULL THEN '27' ELSE '28' END, '0', CURTRXAM, CURTRXAM, CURTRXAM, CURTRXAM, '0' POSTED FROM jcrfp100 WHERE dex_row_id in (SELECT dex_row_id FROM #retentiontemp)

select * from PM10500 where VCHRNMBR in (select VCHRNMBR from #retentiontemp)
select * from JC10500 where VCHRNMBR in (select VCHRNMBR from #retentiontemp)

Inserting multiple lines into a table using declare and result from a select

1 Answers1