setiing variables in a temp table

Question

Post reply

setiing variables in a temp table

Angelindiego

SSCertifiable

Points: 6048
More actions
February 18, 2008 at 3:19 pm

#179305

Hi all. today, I am building a temp table. I would like to set variables in the select in order to use them later in an insert into a static table.
for example:
insert into @tmpTable
(
Name,
Address,
town,
State,
zip
)
select
@Name = t.Name,
Thank you!!,
Angelindiego

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Angelindiego SSCertifiable Points: 6048 More actions · Answer 1

ok, it posted without my ok...and I wasn't finished...LOL

anyhow, you get the idea...

when I try to set the variables, SQL tells me that I cannot do this. I would rather have variables to use later in my code that populates another table.

Thank you for your help!!

Thank you!!,

Angelindiego

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 2

Angelindiego (2/18/2008)
ok, it posted without my ok...and I wasn't finished...LOL
anyhow, you get the idea...
when I try to set the variables, SQL tells me that I cannot do this. I would rather have variables to use later in my code that populates another table.
Thank you for your help!!

Hello Angelindiego,

You can't select that way but it should be like this

if you want to insert the values into a temporary table by selecting from another base table then you need to write the statement as follows:

insert into @tmptable

select col1, col2, col3 from tablename --- Replace the column names and the tablename.

if you want to use the values which you have inserted into the temporary table then you need to declare local variables first and then select the required values into those local variables and finally inserting them into the temporary table. This way you can use the values any number of times but it will be only static. Suppose if you want to use different values then you need to do this in a loop where it will be dynamic values.

Hope this is clear.

Thanks

Lucky

Angelindiego SSCertifiable Points: 6048 More actions · Answer 3

thanks Lucky....I submitted another question, but it didnt post. My computer is having issues today!

Anyhow, will you give me an example of what you mean....you are totally understanding what I am needing....

Thank you!!,

Angelindiego

ksullivan SSCrazy Points: 2346 More actions · Answer 4

create table #test (aaa int, bbb int)

insert into #test select 1, 2

declare

@aaa int,

@bbb int

select @aaa = aaa + 2, @bbb = bbb + 3 from #test

select @aaa aaa, @bbb bbb

insert into #test select @aaa, @bbb

select * from #test

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Ummm... all of that's fine, but it's all RBAR (see my tagline for the explanation).

I assume that you're just a bit new to this. What would be better than assuming that you know what you want to do, is to describe the problem that you're trying to solve and then let us show you the tools for how to do it.

I did the same thing as you when I started... When I asked "How to do assign values found in a row to variables?", my mentor almost killed me on the spot. That's not what strength of a database is. Sure, you can assign row values to variables for a return to the GUI. I just want to make sure that you're not being taught SQL the wrong way to start with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)