Sql Server：选择...Into #TempTable，并对临时表名称进行变量分配

发布于 2024-12-14 06:35:55 字数 1414 浏览 2 评论 0原文

我在这里有一个问题。我最好先粘贴我的代码

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

我尝试使用 PRINT 打印出@Sql 我得到这个结果

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

执行此 sql 后出现错误，我收到错误：

Incorrect syntax near '@TempTable'.

此 # 符号或 INTO 周围的 select 语句内是否有任何约束？

我尝试在 @TempTable 上不进行变量分配，直接将 @TempTable 值放入 sql 中，就像

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

它工作正常一样，我得到了预期的结果？有什么办法可以解决这个问题吗？

感谢您。

PS：我对T-Sql很陌生，请毫不犹豫地指出我的错误。我们都从错误中学习。对于我糟糕的英语水平，我深表歉意。

问候：

梁CK

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

叹梦 2024-12-21 06:35:55

您有几个问题：

您不能指定这样的表名称。您需要将变量分配给表示表名称的字符串： SET @TempTable = ''#'+@TblName+'''; 额外的引号将使表成为字符串
您需要也进行第二个查询动态 SQL。基本上在运行时用表名解析它：

'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'

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'

回复收藏 0 原文

~没有更多了~