?
Solved

Select Into with variable for table_name

Posted on 2007-12-05
4
Medium Priority
?
239 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

743 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