December 1, 2008 at 10:26 pm
hi all,
i need to inner join temp tables to get data set.i have create temp table as follows
declare @TableVariable table(id int,[name] varchar(20))
declare @TableVariable1 table(id int,[name] varchar(20))
insert into @TableVariable values(1,'Microsoft')
insert into @TableVariable values(4,'Microsoft1')
insert into @TableVariable values(2,'Article')
insert into @TableVariable1 values(4,'Microsoft1')
insert into @TableVariable1 values(3,'Microsoft')
and inner join as like normal Inner Join sytax.
select * from @TableVariable inner join @TableVariable1
on @TableVariable.id= @TableVariable1.id
and it compiled without any problem and at run time occur error "varaible @TableVariable are not declared" .and most of SQL function and syntax cant apply to temp tables.please tell me any suitable solution for inner join temp tables and why we cant appply Normal syntax and function to temp tables.
thanks.
December 1, 2008 at 11:01 pm
avhlasith (12/1/2008)
hi all,i need to inner join temp tables to get data set.i have create temp table as follows
declare @TableVariable table(id int,[name] varchar(20))
declare @TableVariable1 table(id int,[name] varchar(20))
insert into @TableVariable values(1,'Microsoft')
insert into @TableVariable values(4,'Microsoft1')
insert into @TableVariable values(2,'Article')
insert into @TableVariable1 values(4,'Microsoft1')
insert into @TableVariable1 values(3,'Microsoft')
and inner join as like normal Inner Join sytax.
select * from @TableVariable inner join @TableVariable1
on @TableVariable.id= @TableVariable1.id
and it compiled without any problem and at run time occur error "varaible @TableVariable are not declared" .and most of SQL function and syntax cant apply to temp tables.please tell me any suitable solution for inner join temp tables and why we cant appply Normal syntax and function to temp tables.
thanks.
use the following code:
declare @TableVariable table(id int,[name] varchar(20))
declare @TableVariable1 table(id int,[name] varchar(20))
insert into @TableVariable values(1,'Microsoft')
insert into @TableVariable values(4,'Microsoft1')
insert into @TableVariable values(2,'Article')
insert into @TableVariable1 values(4,'Microsoft1')
insert into @TableVariable1 values(3,'Microsoft')
select * from @TableVariable t1
inner join @TableVariable1 t2 on t1.id= t2.id
output:
4Microsoft14Microsoft1
December 1, 2008 at 11:05 pm
These are Table Variables, not temporary tables. They are similar but different in some important ways. For instance, table variables are batch-scoped, which means that they go away at the end of the batch. Therefore, you cannot have a GO between where you DECLARE them and where you reference them
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 2, 2008 at 12:17 am
Try this script
declare @TableVariable table(id int,[name] varchar(20))
declare @state varchar(200)
declare @TableVariable1 table(id int,[name] varchar(20))
insert into @TableVariable values(1,'Microsoft')
insert into @TableVariable values(4,'Microsoft1')
insert into @TableVariable values(2,'Article')
insert into @TableVariable1 values(4,'Microsoft1')
insert into @TableVariable1 values(3,'Microsoft')
--and inner join as like normal Inner Join sytax
select t1.id,t1.name from @TableVariable t inner join @TableVariable1 t1
on t.id= t1.id
You need to use alias for tablenames.
December 2, 2008 at 12:25 am
thanks Kishore.P for ur help.it work better.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply