?
Solved

Select Into with variable for table_name

Posted on 2007-12-05
4
Medium Priority
?
245 Views
Last Modified: 2010-04-21
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
0
Comment
Question by:Waterstone
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20413515
>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
 
LVL 17

Expert Comment

by:pssandhu
ID: 20413563
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
 
LVL 17

Expert Comment

by:pssandhu
ID: 20413579
oops typo:
 EXEC 'Select * into ' + @PayTable = 'from InputTable'

but I guess angels got it
0
 

Author Closing Comment

by:Waterstone
ID: 31412916
Thanks!  Exactly what I needed
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question