CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rahul Tripathi
Rahul Tripathi

Reputation: 172378

Check if a value is present in any of the column in the table

I have a table with more than 200 columns. I am populating the table with the help of a text file. The data is getting inserted into the table successfully.

My problem is that I want to check if a particular value from the text file is existing in the table or not. I know that some of you might say that I can check it that you can map the column name from the code and then use the column name and check like this:

select 1 from table_name where column_name like '%value%'

But I dont have the access to the code. I just have the text file and the database. So I have to check the value in the database only. One way which is tedious and lengthy is to go one by one to each 200 column and then execute like above. Is there any other way?

Upvotes: 0

Views: 1871

Answers (4)

mhn
mhn

Reputation: 2750

How about generating 200 odd scripts depending on your table? Below script can be useful

With Sample_CTE as (
select a.TABLE_NAME,b.COLUMN_NAME from  INFORMATION_SCHEMA.TABLES a inner join
     INFORMATION_SCHEMA.COLUMNS b  on a.TABLE_NAME = b.TABLE_NAME and a.TABLE_NAME = 'MYTABLENAME')
     SELECT 'SELECT TABLE_NAME,COLUMN_NAME from ' + TABLE_NAME + ' where '+ COLUMN_NAME +' like  ''%value%''' from Sample_CTE

Upvotes: 1

Jayvee
Jayvee

Reputation: 10875

this will give you a true/false result:

DECLARE @tablexml XML = (SELECT * FROM yourtable FOR XML PATH(''))
SELECT @tablexml.query('//*="valuetosearchfor"')

Upvotes: 1

user2110298
user2110298

Reputation: 567

If I understand this correctly then you have a table with 200 columns. In that table you put some data from a text file, meaning that x number of rows are populated in that table depending on the size of the text file.

Now you want to find a certain value in the table.

If you're not very familiar with SQL you could try a tool like this one: SQL locator.

Upvotes: 0

user3485087
user3485087

Reputation: 16

What type of file is it? You can use the SQL data importer to import the file to a new table. Once you have the new table you can INNER JOIN on both of the table to compare the data sets.

Upvotes: 0

Related Questions