Error: "No column name was specified for column 1 of 'SOURCE'" in SQL Server Merge statement
when I was working with Merge statement with SQL Server 2008 then while writing this I got this error and after couple of min i got the issues
when I was working with Merge statement with SQL Server 2008 then while writing this I got this error and after couple of min i got the issues
Declare
@Id Int = 10,
@Name Varchar(20) = 'Kesharwani',
@Salary Int = 30000
MERGE INTO [Temp] as TARGET
USING (SELECT @Id,@Name,@Salary) AS
SOURCE ON TARGET.empId = SOURCE.empId
WHEN MATCHED THEN
UPDATE SET Name=SOURCE.Name, Salary =SOURCE.Salary
WHEN NOT MATCHED THEN
INSERT (empId,Name,Salary)
VALUES (SOURCE.empId,SOURCE.Name,SOURCE.Salary);
While running above query sometime I got this error: No column name was specified for column 1 of 'SOURCE'.
The problem in the above query the alias name not there on the columns in Select statement, to resolve this error above query can be written in following way
Declare
@Id Int = 10,
@Name Varchar(20) = 'Kesharwani',
@Salary Int = 30000
MERGE INTO [Temp] as TARGET
USING (SELECT @Id AS empId,@Name AS Name,@Salary AS Salary) AS
SOURCE ON TARGET.empId = SOURCE.empId
WHEN MATCHED THEN
UPDATE SET Name=SOURCE.Name, Salary =SOURCE.Salary
WHEN NOT MATCHED THEN
INSERT (empId,Name,Salary)
VALUES (SOURCE.empId,SOURCE.Name,SOURCE.Salary);
No comments:
Post a Comment