为什么我的alter table语句未正确在临时表中添加列？ [英] Why is my alter table statement not properly adding a column to my temporary table?

问题描述

我在此存储过程的开头创建了一个临时表。它创建成功，可以从中选择和插入。

I have a temporary table created at the beginning of this stored procedure. It is created successfully and can be selected from and inserted to.

这是create语句

CREATE TABLE #tmpImportData (GuideFirstName VARCHAR(MAX), GuideLastName VARCHAR(MAX), 
                             email VARCHAR(MAX), group_id_text VARCHAR(MAX), CandidateName VARCHAR(MAX), 
                             grade_text VARCHAR(5), dateofbirth DATE

                             )

我的问题是在更改临时表后尝试更新列。我收到错误

My problem is trying to update a column after I alter the temporary table. I get the error

消息207，级别16，状态1

无效的列名

Msg 207, Level 16, State 1
invalid column name

代码：

declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData 
ADD group_id INT
ALTER TABLE #tmpImportData 
ADD guide_id INT
ALTER TABLE #tmpImportData 
ADD password_plain_text VARCHAR(500)
ALTER TABLE #tmpImportData 
ADD guide_email VARCHAR(500)
ALTER TABLE #tmpImportData 
ADD class_id INT'

exec sp_executesql @Sql1

UPDATE #tmpImportData 
SET group_id = CAST(group_id_text AS INT)

UPDATE #tmpImportData 
SET group_id = 0 WHERE group_id IS NULL

推荐答案

尝试这样做

declare @SQl1 nvarchar(max)
set @SQL1 ='
ALTER TABLE #tmpImportData 
ADD ['+@group_id+'] INT
ALTER TABLE #tmpImportData 
ADD ['+@guide_id+'] INT
ALTER TABLE #tmpImportData 
ADD ['+@password_plain_text+'] VARCHAR(500)
ALTER TABLE #tmpImportData 
ADD ['+@guide_email+'] VARCHAR(500)
ALTER TABLE #tmpImportData 
ADD ['+@class_id+]' INT'

exec  @Sql1

UPDATE #tmpImportData 
SET group_id = CAST(group_id_text AS INT)

UPDATE #tmpImportData 
SET group_id = 0 WHERE group_id IS NULL

这篇关于为什么我的alter table语句未正确在临时表中添加列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！