Dynamic table name in select command

Posted by Guna27 under Sql Server on 12/26/2012 | Points: 10 | Views : 3384 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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..............

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

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

0	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

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

0	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

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

Latest Posts