Select Into with variable for table_name

Hi,

I need to use a variable as the table name for a select into statement.

DECLARE      
   @WEDate NVarChar(8),
   @PayPrefix    NVarChar(10),
   @PayTable    NVarChar(20)

 set @WEDate  = '20071201'
set @PayPrefix   = 'XXX'
 set @PayTable   = 'Pay_'+@PayPrefix+@WEDate
/* @PayTable now = Pay_XXX20071201 */  

Select *
 into @PayTable
from InputTable

I think I need to use  exec sp_executesql
but need some help on the correct format, unless there is a better way.

Thanks
WaterstoneAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>I think I need to use  exec sp_executesql
it will be dynamic sql, indeed:


DECLARE      
   @WEDate NVarChar(8), 
   @PayPrefix    NVarChar(10),
   @PayTable    NVarChar(20) 
 set @WEDate  = '20071201'
set @PayPrefix   = 'XXX'
 set @PayTable   = 'Pay_'+@PayPrefix+@WEDate
/* @PayTable now = Pay_XXX20071201 */   
exec('Select *  into ' + @PayTable + ' from InputTable ')

Open in new window

0
 
pssandhuCommented:
Try This:

DECLARE      
   @WEDate      NVarChar(8),
   @PayPrefix    NVarChar(10),
   @PayTable    Sysname

  set @WEDate  = '20071201'
  set @PayPrefix   = 'XXX'
  set @PayTable   = 'Pay_'+@PayPrefix+@WEDate

  EXEC 'Select * into @PayTable from InputTable'
0
 
pssandhuCommented:
oops typo:
 EXEC 'Select * into ' + @PayTable = 'from InputTable'

but I guess angels got it
0
 
WaterstoneAuthor Commented:
Thanks!  Exactly what I needed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.