Solved

Select Into with variable for table_name

Posted on 2007-12-05
4
221 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 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

856 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