SQL Server Query column from Select Can anyone please guide me what's wrong in this SQL Server query:
SELECT 
    A.*,
    (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) Name 
FROM A;

It seems that A.Name is inaccessible in the query.
I want to populate a column in select query with data from another table. I can't use INNER JOIN since there isn't any FK relationship between tables.
Actually I want to insert data from table A into another table C, but for insert I need to populate a NOT NULL Column in table C, that I am trying to populate with this query (SELECT TOP 1 B.Name FROM B WHERE Contains(B.Name,A.Name)) as column.
Any help would be really appreciated.
The problem is just as the error message explains. The second argument of CONTAINS must be a literal string or a variable. It’s not the case that everywhere a string is allowed in T-SQL that you can also use a column name.
You could do something kludgy like this, but performance would likely be abominable.
CREATE FUNCTION DoesContain(@arg VARCHAR(30))
RETURNS tinyint AS BEGIN
  RETURN
    CASE WHEN EXISTS (
      SELECT * FROM B
      WHERE CONTAINS([Name],@arg)
    ) THEN 1 ELSE 0 END
END;
GO

SELECT
  A.*,
  (SELECT TOP 1 B.Name FROM B WHERE dbo.DoesContain(A.Name)=1) as [Name]
FROM A;