Select Into with variable for table_name

Posted on 2007-12-05
Last Modified: 2010-04-21

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

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

Question by:Waterstone
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
LVL 143

Accepted Solution

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:

   @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

LVL 17

Expert Comment

ID: 20413563
Try This:

   @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'
LVL 17

Expert Comment

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

but I guess angels got it

Author Closing Comment

ID: 31412916
Thanks!  Exactly what I needed

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Record open by another user 6 82
Why is the output of this function is like this? 4 47
sql query help 2 66
What is this datetime? 1 33
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 …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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