# Sql-server – SQL Server Query column from Select

sql servert-sql

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;