Dynamic table name in select command

Posted by Guna27 under Sql Server on 12/26/2012 | Points: 10 | Views : 2295 | Status : [Member] | Replies : 4
I have a select command to select and insert into a variable with table data type from a dynamic table.


declare @RegisterNumber table(RegNo bigint);
insert into @RegisterNumber (RegNo) (select RegisterNo from dbo.Student )

This command works fine.

But I want to mention the dynamic table name it shows an error.
declare @TabName='dbo.Student';
exec('insert into @RegisterNumber(RegNo) (select RegisterNo from '+@TabName+')

Guna..............


Responses

Posted by: Sinisreedhar on: 12/26/2012 [Member] Starter | Points: 25

Up
0
Down
But I want to mention the dynamic table name it shows an error.
declare @TabName='dbo.Student';
exec('insert into @RegisterNumber(RegNo) (select RegisterNo from '+@TabName+')+' )

Ready to accept challenges at any time.....

Guna27, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Guna27 on: 12/26/2012 [Member] Starter | Points: 25

Up
0
Down
This is my Command...

declare @RegisterNumber table(RegNo bigint);
declare @Query nvarchar(100);
declare @TabName varchar(50);
set @TabName=dbo.Student
set @Query='insert into @RegisterNum(Reg) (select RegisterNo from '+@TabName+')';
exec sp_executesql @Query,N'@RegisterNum table(Reg bigint) output',@RegisterNum=@RegisterNumber output;

Error Message:
Must declare the scalar variable "@RegisterNumber".

Guna..............

Guna27, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 12/27/2012 [Member] Bronze | Points: 25

Up
0
Down
declare @RegisterNumber table(RegNo bigint);
declare @Query nvarchar(100);
declare @TabName varchar(50);
set @TabName = 'dbo.Student'
set @Query='insert into @RegisterNum(Reg) values(select RegisterNo from ' + @TabName + ')';
print @Query --***output*** insert into @RegisterNum(Reg) values(select RegisterNo from dbo.Student)

Thanks
Pavan Kumar
Mark Answer if this fits the need

Guna27, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 12/27/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

if you want to execute the dynamic query ..

Include all the lines starting from the declaration of the variable to the assigning values then
use the sp_executeSQL @query_variable

look at the following example....



Declare @query nvarchar(max)

Declare @table varchar(50)
set @table = 'dbo.student'

set @query='
Declare @temp table(sno int); Insert into @temp(sno) select sid from '+@table+'; '

set @query=@query+' select * from @temp;'

print @query

exec sp_executeSQL @query


Sourabh07

Guna27, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response