Using Dynamic Table Name in Select Into

• sqlcentraldog

  Old Hand

  Points: 334

  More actions

  July 28, 2010 at 4:47 pm

  #238146

  I have a stored procedure that accepts one parameter. I want to create a table using static text, the parameter value and the current date. I can create the table name value in a SYSNAME data type but when I try to use the declared variable, @TableName, in the SELECT INTO statement it errors off. What do I need to do to reference the dynamic table name? Partial code below

  ALTER PROCEDURE [dbo].[procPros2ActiveProsCasesBySpecificCounty]

  -- Last three numbers in County FIP, 53 for WA not needed

  @pCountyFIP char(3) = null

  AS

  BEGIN

  -- SET NOCOUNT ON added to prevent extra result sets from

  -- interfering with SELECT statements.

  SET NOCOUNT ON;

  DECLARE @TableName SYSNAME

  SET @TableName = 'ProsActiveCasesFor53'

  + @pCountyFIP

  + 'For'

  + convert(varchar(2),month(getdate()))

  + '/'

  + convert(varchar(2),day(getdate()))

  + '/'

  + convert(varchar(4),year(getdate()))

  -- Insert statements for procedure here

  SELECT CASE_NO

  INTO @TableName

  FROM SEHC

  WHERE FIP = @pCountyFIP

  END

• LutzM

  SSC Guru

  Points: 107049

  More actions

  July 28, 2010 at 5:14 pm

  #1199539

  You would need to use dynamic SQL.

  Something like:

  DECLARE @sql VARCHAR(500)

  -- Insert statements for procedure here

  SET @sql=

  'SELECT CASE_NO

  INTO [' +@TableName + ']

  FROM SEHC

  WHERE FIP = ' + @pCountyFIP +'''

  EXEC(@sql)

  Side note: do you really have a table [ProsActiveCasesFor53100For 7/29/2010]??? :pinch:

  I strongly recommend to rethink your table name convention as well as your database design in general if you really have one table per DAY per Count... I'm scared...

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• sqlcentraldog

  Old Hand

  Points: 334

  More actions

  July 30, 2010 at 8:47 am

  #1200397

  Perfect, got what I needed. As far as your concerns these are ad hoc data requests with the user needing the date in the table name. These are temporary tables that are shipped out and deleted. Am in the process of building the front end that will allow the user to access data in real time.

  Once again thanks