无效的列名AgtTableId
无效的列名AgtTableId
提问于 2014-05-30 23:54:14
我正在尝试通过T-SQL脚本更改特定表的模式。我想创建一个临时表，将相关数据转储到该表中，删除/重新创建原始表，然后传输数据。
只要我不运行最后的INSERT语句，这个脚本似乎就能正常工作。
INSERT INTO AgentRelationshipCodes(RelationshipId, EffectiveDate, LastChangeDate, LastChangeId, AgtTableId)
SELECT RelationshipId, EffectiveDate, LastChangeDate, LastChangeId, ID
FROM tmpAgentRelationshipCodes这时，我得到了错误消息
Msg 207, Level 16, State 1, Line 26
Invalid column name 'AgtTableId'.但是，我不确定为什么，因为我在第一个CREATE TABLE语句中清楚地创建了这个字段。另外，如果我单独运行脚本的其余部分(主要是创建两个表，将数据转储到临时表中)和INSERT语句，它们将会正常工作。
CREATE TABLE tmpAgentRelationshipCodes
(
ID int,
RelationshipId char(3),
EffectiveDate datetime,
LastChangeDate datetime,
LastChangeId char(6)
);
INSERT INTO tmpAgentRelationshipCodes(ID, RelationshipId, EffectiveDate, LastChangeDate, LastChangeId)
SELECT ID, RelationshipId, EffectiveDate, LastChangeDate, LastChangeId
FROM AgentRelationshipCodes
DROP TABLE AgentRelationshipCodes
CREATE TABLE AgentRelationshipCodes
(
Id UNIQUEIDENTIFIER DEFAULT NEWID(),
RelationshipId char(3) NULL,
EffectiveDate datetime NULL,
LastChangeDate datetime NOT NULL,
LastChangeId char(6) NOT NULL,
AgtTableId int FOREIGN KEY REFERENCES AgentTransmission(ID)
);
INSERT INTO AgentRelationshipCodes(RelationshipId, EffectiveDate, LastChangeDate, LastChangeId, AgtTableId)
SELECT RelationshipId, EffectiveDate, LastChangeDate, LastChangeId, ID
FROM tmpAgentRelationshipCodes回答 1
Stack Overflow用户
发布于 2014-06-04 00:10:19
按如下所示编辑脚本以包括dbo，并且还需要使用GO语句来分隔命令。
CREATE TABLE [dbo].[tmpAgentRelationshipCodes]
(
ID int,
RelationshipId char(3),
EffectiveDate datetime,
LastChangeDate datetime,
LastChangeId char(6)
);
GO
INSERT INTO [dbo].[tmpAgentRelationshipCodes](ID, RelationshipId, EffectiveDate, LastChangeDate, LastChangeId)
SELECT ID, RelationshipId, EffectiveDate, LastChangeDate, LastChangeId
FROM [dbo].[AgentRelationshipCodes]
GO
DROP TABLE [dbo].[AgentRelationshipCodes]
CREATE TABLE [dbo].[AgentRelationshipCodes]
(
Id int PRIMARY KEY,
RelationshipId char(3) NULL,
EffectiveDate datetime NULL,
LastChangeDate datetime NOT NULL,
LastChangeId char(6) NOT NULL,
AgtTableId int FOREIGN KEY REFERENCES AgentTransmission(ID)
ON DELETE CASCADE
)
GO
INSERT INTO [dbo].[AgentRelationshipCodes](RelationshipId, EffectiveDate, LastChangeDate, LastChangeId, AgtTableId)
SELECT RelationshipId, EffectiveDate, LastChangeDate, LastChangeId, ID
FROM [dbo].[tmpAgentRelationshipCodes]页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/23958625
复制相关文章
相似问题
腾讯云开发者
