SQL Server 2008中的合并语句错误(语法附近不正确)
问题描述:
我试图在两个临时表中使用“MERGE”,但未能获得结果。SQL Server 2008中的合并语句错误(语法附近不正确)
错误:关键字'AS'附近的语法不正确。
Create Table #tmp1
(
[Server] varchar(4),
[DateTime] datetime,
IdComponent int,
AvgTimeTaken int
)
Create Table #tmp2
(
[Server] varchar(4),
[DateTime] datetime,
IdComponent int,
AvgTimeTaken int
)
insert into #tmp1 values ('BE01','2012-08-01 00:00:00',1,100)
insert into #tmp1 values ('BE02','2012-08-01 00:00:00',2,100)
insert into #tmp1 values ('BE03','2012-08-01 00:00:00',3,100)
insert into #tmp1 values ('BE04','2012-08-01 00:00:00',4,100)
insert into #tmp1 values ('BE05','2012-08-01 00:00:00',5,100)
insert into #tmp2 values ('BE01','2012-08-01 00:00:00',1,100)
insert into #tmp2 values ('BE02','2012-08-01 00:00:00',2,200)
insert into #tmp2 values ('BE03','2012-08-01 00:00:00',3,300)
insert into #tmp2 values ('BE04','2012-08-01 01:00:00',4,400)
insert into #tmp2 values ('BE05','2012-08-01 02:00:00',5,500)
MERGE #tmp1 AS [Target]
USING #tmp2 AS [Source]
ON ([Target].[Server] = [Source].[Server]
AND [Target].[DateTime] = [Source].[DateTime]
AND [Target].[IdComponent] = [Source].[IdComponent])
WHEN MATCHED THEN
UPDATE
SET [Target].AvgTimeTaken = [Source].AvgTimeTaken
WHEN NOT MATCHED THEN
INSERT ([Target].[Server], [Target].[DateTime], [Target].IdComponent, [Target].AvgTimeTaken)
VALUES ([Source].[Server], [Source].[DateTime], [Source].IdComponent, [Source].AvgTimeTaken);
我不知道我可能是错的。最后还有一个分号。 请帮忙!
答
取出INSERT
子句中的Target
别名。
Create Table #tmp1 (
[Server] varchar(4),
[DateTime] datetime,
IdComponent int,
AvgTimeTaken int
);
Create Table #tmp2 (
[Server] varchar(4),
[DateTime] datetime,
IdComponent int,
AvgTimeTaken int
);
insert into #tmp1 values ('BE01','2012-08-01 00:00:00',1,100);
insert into #tmp1 values ('BE02','2012-08-01 00:00:00',2,100);
insert into #tmp1 values ('BE03','2012-08-01 00:00:00',3,100);
insert into #tmp1 values ('BE04','2012-08-01 00:00:00',4,100);
insert into #tmp1 values ('BE05','2012-08-01 00:00:00',5,100);
insert into #tmp2 values ('BE01','2012-08-01 00:00:00',1,100);
insert into #tmp2 values ('BE02','2012-08-01 00:00:00',2,200);
insert into #tmp2 values ('BE03','2012-08-01 00:00:00',3,300);
insert into #tmp2 values ('BE04','2012-08-01 01:00:00',4,400);
insert into #tmp2 values ('BE05','2012-08-01 02:00:00',5,500);
MERGE #tmp1 AS Target
USING #tmp2 AS Source
ON (Target.Server = Source.Server
AND Target.DateTime = Source.DateTime
AND Target.IdComponent = Source.IdComponent)
WHEN MATCHED THEN
UPDATE
SET Target.AvgTimeTaken = Source.AvgTimeTaken
WHEN NOT MATCHED THEN
INSERT (Server, DateTime, IdComponent, AvgTimeTaken)
VALUES (Source.Server, Source.DateTime, Source.IdComponent, Source.AvgTimeTaken);
答
我碰到以前这个错误,这是因为SQL Server 2008中,除非你通过执行以下设置兼容模式R2不支持合并语法:
ALTER DATABASEMyDatabase的SET COMPATIBILITY_LEVEL = 100
凡MyDatabase的是要将此应用到数据库的名称。
答
MERGE语句中使用的插入列表不能包含多部分标识符。改用单个零件标识符。
还在* MERGE上的语句*中放置了分号。你应该养成在T-SQL中总是正确地终止语句的习惯 - 这些需求随着每个新版本的增加而增加,并且随处可见。 – 2012-08-03 02:56:29