SQL Server- Insert results from dynamic query and variable into same row of a temporary table

Question

How do you insert the results of a dynamic SQL server query and a variable into the same row of a temp table.

Here is the format of the temporary table (#temp)

create TABLE #temp (YEAR INT, ACC_AMOUNT Money, REJ_AMOUNT Money,  OUT_AMOUNT Money,TOT_AMOUNT   Money)

The Stored PROC is executed by

exec  sp_executesql @pSQL

and will return values that should go into the columns ACC_AMOUNT, REJ_AMOUNT, OUT_AMOUNT.

14569849.11           696235.49             1353464.92            16619549.52

I want to insert a variable (@pACCU_YEAR) for the Year and the results of the SP and insert the dyanmic query in the same Row. This is wrong, but this will give you an idea of what I mean.

insert into #temp (YEAR)
values (@pACCU_YEAR)

insert into #temp (ACC_AMOUNT , REJ_AMOUNT,  OUT_AMOUNT,TOT_AMOUNT)
exec  sp_executesql @pSQL

This will result in

YEAR        ACC_AMOUNT            REJ_AMOUNT            OUT_AMOUNT            TOT_AMOUNT
2014        NULL                  NULL                  NULL                  NULL
NULL        14569849.11           696235.49             1353464.92            16619549.52

I would like:

YEAR        ACC_AMOUNT            REJ_AMOUNT            OUT_AMOUNT            TOT_AMOUNT
2014        14569849.11           696235.49             1353464.92            16619549.52

I am a little new to SQL, can you please show me an example of how to select a column that is a variable not within the table? — CompChemist
– CompChemist, Commented Jul 30, 2014 at 17:57
Ok, Thanks this worked. I didn't know you could set a variable as part of the select statement. I will use the code suggested by Daniel E. — CompChemist
– CompChemist, Commented Jul 30, 2014 at 18:03

Daniel E. · Accepted Answer · 2014-07-30 17:59:37Z

2

Select @Year [Year]
    ,Acc_Amount
    ,other fields....
FROM #Temp

In response to the question asked in your comment.

Use this after the @pSql is run. Leave the column Year out of the declaration.

Alternatively, after it runs just:

update #temp
SET [Year]=@Year

answered Jul 30, 2014 at 17:59

Daniel E.

2,0793 gold badges22 silver badges28 bronze badges

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

Comments

Rahul · Accepted Answer · 2014-07-30 17:58:17Z

1

What's the code inside @pSQL? Why don't do the INSERT in your dynamic query itself like

declare @pSQL varchar(max);

set @pSQL = 'some sql command;
some_other_sql_command;
.......

insert into #temp (ACC_AMOUNT , REJ_AMOUNT,  OUT_AMOUNT,TOT_AMOUNT)
select blah,blah1,blah2,blah3
from blah_table;';


exec  sp_executesql @pSQL;

can you please show me an example of how to select a column that is a variable not within the table?

use same SELECT statement like

select @var1 as some_name, @var2 as some_other_name

as some_name is optional. That's to give a meaningful name to the selected field.

answered Jul 30, 2014 at 17:58

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Collectives™ on Stack Overflow

SQL Server- Insert results from dynamic query and variable into same row of a temporary table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related