Friday, February 08, 2013

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 

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: