Inserting data in temp table in dynamic query

Question

I have this query

declare @SQL  as NVARCHAR(MAX);

create table #TempCalcPerc
( 
    PctAPAC nvarchar(50),
    PctEMEA nvarchar(50),
    PctLAmerica nvarchar(50),
    PctNAmerica nvarchar(50)
)

set @SQL = 'Insert into 
#TempCalcPerc(PctAPAC,PctEMEA,PctLAmerica,PctNAmerica)
select RTrim(LTrim(Replace([rRecurringMarginPct-Apac],''%'',''))),
RTrim(LTrim(Replace([rRecurringMarginPct-Emea],''%'',''))),
RTrim(LTrim(Replace([rRecurringMarginPct-LatinAmerica],''%'',''))),
RTrim(LTrim(Replace([rRecurringMarginPct-NorthAmerica],''%'','')))
from DashboardData
where DataType = ''SLPayroll'''

exec sp_executeSQL @SQL;

I am basically just trying to insert some data into a temp table after some replace operation.

Although I have 4 columns selected in the select query, I get this error:

The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

If I execute the SQL normally without it being dynamic the query runs fine. Can someone please have a look and let me know what I am doing wrong here.

Also if I change the query to be the insert seems to work fine.

set @SQL = 'Insert into 
    #TempCalcPerc(PctAPAC,PctEMEA,PctLAmerica,PctNAmerica)
    select 1,2,3,4'

exec sp_executeSQL @SQL;

Thanks

I have to have some dynamic variables inside the query. Over here I was just trying to simplify the question. — SP1
– SP1, Commented Jan 15, 2019 at 18:15
Very good idea..I found the issue..The replace function was supposed to have instead of ' ' '' '' in the end. Thank you so much. — SP1
– SP1, Commented Jan 15, 2019 at 18:23
BOOM! the number 1 piece of advice for debugging dynamic sql is to review the print statement often. — Doug Coats
– Doug Coats, Commented Jan 15, 2019 at 18:25

Kostya · Accepted Answer · 2019-01-15 18:25:40Z

1

how about :

Create table #TempCalcPerc
( 
  PctAPAC nvarchar(50),
  PctEMEA nvarchar(50),
  PctLAmerica nvarchar(50),
  PctNAmerica nvarchar(50)
)

declare @SQL  as NVARCHAR(MAX);
SET @SQL = 'Insert into 
#TempCalcPerc(PctAPAC,PctEMEA,PctLAmerica,PctNAmerica)
select RTrim(LTrim(Replace([rRecurringMarginPct-Apac],''%'',''''))),
RTrim(LTrim(Replace([rRecurringMarginPct-Emea],''%'',''''))),
RTrim(LTrim(Replace([rRecurringMarginPct-LatinAmerica],''%'',''''))),
RTrim(LTrim(Replace([rRecurringMarginPct-NorthAmerica],''%'','''')))
from DashboardData
where DataType = ''SLPayroll'''

exec @SQL;

answered Jan 15, 2019 at 18:25

Kostya

1,6051 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting data in temp table in dynamic query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related