CODEWITHSUNDEEP
sqlsql-server-2008t-sqlvariablestemp-tables
Worgon
Worgon

Reputation: 1657

Sql Server: Select..Into #TempTable with variable assignment on Temp Table name

I have a question here.I better paste my code first

SET @Sql = 'DECLARE @Date_From VARCHAR(10);
            DECLARE @Date_To VARCHAR(10);
            DECLARE @TempTable VARCHAR(500);
            SET @TempTable = #'+@TblName+'; 
            SET @Date_From = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-@DayPrior,120)+''';
            SET @Date_To = '''+CONVERT(VARCHAR(10),DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-@DayPrior,120)+''';
            '+ @Sql

I try to print out @Sql using PRINT and i get this result

DECLARE @Date_From VARCHAR(10);
DECLARE @Date_To VARCHAR(10);
DECLARE @TempTable VARCHAR(500);
SET @TempTable = #RawData_New; 
SET @Date_From = '2011-04-05';
SET @Date_To = '2011-04-05';

Select Distinct Coloum1,Coloum2 into @TempTable 
from RawData_New with(nolock)  
here Convert(varchar(10), Date_Tm, 120) Between @Date_From And @Date_To

I get an error after executing this sql,i get an error:

Incorrect syntax near '@TempTable'.

Is there any Constraint to this # symbol or inside the select Statement around INTO?

I tried that not variable assigment on @TempTable that i directly put the @TempTable Value inside the sql like 

Select Coloum1,Coloum2 into #RawData_New
from RawData_New with(nolock)  
here Convert(varchar(10), Date_Tm, 120) Between @Date_From And @Date_To

Its works fine and i get my expected result? is there any solution to solve this?

Thanks you.

PS: I am quite new to T-Sql, Please dont hesitated to point out my mistake.We all Learn from Mistake.Any Sorry for my poor English Level.

Regards:

LiangCK

Upvotes: 1

Views: 6047

Answers (1)

JNK
JNK

Reputation: 65157

You have a couple of issues:

  • You can't assign a table name like that. You need to assign the variable to the string representing the table name: SET @TempTable = ''#'+@TblName+'''; The extra quotes will make the table a string

  • You need to make the second query Dynamic SQL as well. Basically parse it with the table name at runtime:

..

'Select Coloum1,Coloum2 into' + @TableName +'
from SPC.dbo.Lube_RawData_New with(nolock)  
where Convert(varchar(10), Date_Tm, 120) Between @Date_From And @Date_To'

Upvotes: 4

Related Questions