Query - Temp table - SQL Server Forums

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)

Query - Temp table

Author

Topic

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-01-31 : 12:42:10

hi guys,

i have to insert some rows into temptable which is not created in database but i have to store some values from diffferent tables using joins...but it gives me error..can anyone help me in this matter where i m wrong...

create proc sp_tab
as
begin

declare
@num char(10),
@names char(50),
@addr char(100),
@tcity char(50),
@phone char(25),
@city char(50),
@state char(3),
@zp char(5)

insert into #tabunknown
(
num,
names,
addr,
tcity,
phone,
city,
state,
zp
)

select
(
t.num,
t.names,
t.addr,
t.tcity,
t.phone,
t1.city,
t1.state,
t1.zp
)
from tabt t
left outer join
(select city,state,zp from tabv v where pin = '123') t1
on t.tcity = t1.city

where t.names in ('absi','werwe','gerti')
and t.num in ('1001','10001')

can anyone help me?
thanks.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-31 : 13:41:36

(1) I dont see where you are creating the temp table?
(2) remove the brackets around column names in the SELECT.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-01-31 : 15:08:50

quote:
Originally posted by dinakar

(1) I dont see where you are creating the temp table?
(2) remove the brackets around column names in the SELECT.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

i created this temp table like this:

create table #tabunkown
(
num char(10),
names char(50),
addr char(100),
tcity char(50),
phone char(25),
city char(50),
state char(3),
zp char(5)
)

and removed brackets from select...now i m not getting error..
Thanks for ur help...

but don't know not giving result correct..actually 'absi','werwe','gerti' - i have used in query...that is in one table - all 3col names..so instead of values i have to use that table..is
namestab
---------
absi
werwe
gerti

now can u tell me how can i join this table including all colnames in query..

thanks a lot!!!.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-31 : 15:13:55

can you post your modified code...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-01-31 : 15:42:04

quote:
Originally posted by dinakar

can you post your modified code...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

here it is:

create proc sp_tab
as
begin

declare
@num char(10),
@names char(50),
@addr char(100),
@tcity char(50),
@phone char(25),
@city char(50),
@state char(3),
@zp char(5)

insert into #tabunknown
(
num,
names,
addr,
tcity,
phone,
city,
state,
zp
)

select
t.num,
t.names,
t.addr,
t.tcity,
t.phone,
t1.city,
t1.state,
t1.zp

from tabt t
left outer join
(select city,state,zp from tabv v where pin = '123') t1
on t.tcity = t1.city

where t.names [from namestab{table}]
and t.num [from numstab{table}]

in namestab{table}-
-----------------
(columns)
absi
werwe
gerti

and in numstab{table}
---------------------
(columns)
1001
10001

so i have to use this table including columns not the actual values of columns..

now can u give me some idea??

thanks a lot for ur help and suggetions....

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-31 : 18:12:29

You mentioned you created the temp table..I dont see it in the code? Is it outside this proc? If so where? Is it within the scope of execution?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-31 : 18:14:57

Something like this:



create proc sp_tab
as
begin

declare
	@num char(10),
	@names char(50),
	@addr char(100),
	@tcity char(50),
	@phone char(25),
	@city char(50),
	@state char(3),
	@zp char(5)

create table #tabunkown (
	num char(10),
	names char(50),
	addr char(100),
	tcity char(50),
	phone char(25),
	city char(50),
	state char(3),
	zp char(5)
)

INSERT INTO #tabunknown
	(num,names,addr,tcity,phone,city,state,zp)
SELECT t.num,t.names,t.addr,t.tcity,t.phone,t1.city,t1.state,t1.zp
FROM tabt t
LEFT OUTER JOIN
(select city,state,zp from tabv v where pin = '123') t1 on t.tcity = t1.city
JOIN namestab on namestab.names = T.names
JOIN numstab on numstab.num = t.num

End

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-31 : 18:15:47

And, btw, dont name your procs with "sp_...". SQL Server thinks its a system proc and looks for it under master db. So save some trouble for SQL Server.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

khusiie
Yak Posting Veteran

78 Posts

Posted - 2008-02-01 : 09:53:34

thanks for ur help and suggetion..it really helped..and gave me correct result..ya, i will make sure not to give proc name - sp..

thanks a lot dinakar!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources