store data in a temporary table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

store data in a temporary table

Author

Topic

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-21 : 13:16:41

I created a script

select ltrim(rtrim(cast(clt_id as varchar(10)))) + ltrim(rtrim(convert(char, cast(clt_ref_no as varchar(10))))) as account, max(list_date) as max_list_date, status_code from dm.debt
where clt_id like 'wsp%' and status_code='520'
group by clt_ref_no, clt_id, status_code
order by clt_ref_no desc

and it outputs
account max_list_date status_code
105R5681 2006-01-15 110
105P8541 2006-02-04 110

I want to create and store this data in a temporary table so I can match it with an existing table. However, I want the fields account and max_list_date created because they are just being derived from combine fields and I believe they might be labels only.

I was looking at using the command to create view [name of table] as but I don't know if that will work.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 13:18:17

select ltrim(rtrim(cast(clt_id as varchar(10)))) + ltrim(rtrim(convert(char, cast(clt_ref_no as varchar(10))))) as account, max(list_date) as max_list_date, status_code

INTO MY_TEMP_TABLE

from dm.debt
where clt_id like 'wsp%' and status_code='520'
group by clt_ref_no, clt_id, status_code
order by clt_ref_no desc

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-21 : 14:17:14

I am getting a variable 'my_temp_table' not found error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 14:22:58

quote:
Originally posted by pinoyextacy

I am getting a variable 'my_temp_table' not found error

that was for demonstration purpose.replace it with your temporary table name and remember to precede by #

select ltrim(rtrim(cast(clt_id as varchar(10)))) + ltrim(rtrim(convert(char, cast(clt_ref_no as varchar(10))))) as account, max(list_date) as max_list_date, status_code 

INTO #YourTemporaryTableNameHere
from dm.debt
where clt_id like 'wsp%' and status_code='520'
group by clt_ref_no, clt_id, status_code
order by clt_ref_no desc

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-21 : 14:30:36

it executed but now I don't know how to access it or where it is

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 14:33:38

quote:
Originally posted by pinoyextacy

it executed but now I don't know how to access it or where it is

select ltrim(rtrim(cast(clt_id as varchar(10)))) + ltrim(rtrim(convert(char, cast(clt_ref_no as varchar(10))))) as account, max(list_date) as max_list_date, status_code 

INTO #YourTemporaryTableNameHere
from dm.debt
where clt_id like 'wsp%' and status_code='520'
group by clt_ref_no, clt_id, status_code
order by clt_ref_no desc

select * from #YourTemporaryTableNameHere

Please note that this table will be available only for the current connection.

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-21 : 14:50:01

THAT WORKS THANKS

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-21 : 15:49:29

quote:
Originally posted by visakh16

that was for demonstration purpose.replace it with your temporary table name and remember to precede by #

no...It should have created a table

pinoyextacy
Starting Member

15 Posts

Posted - 2008-07-21 : 16:38:32

Now, how do you process two temporary tables on the same connection in which it pops two screens

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 16:47:29

What do you mean by "it pops two screens"?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources