I am not a database administrator. Most of my experience is with MySQL. However I think what I want to do is in the realm of possibility.

I want to text query a single large table in a SQL Server database for about 30 keyword terms. I then want the results to be dumped into a new table on the same database. I want the terms found to include entire field they were found in. (other items in row don't matter).

I am trying to produce data from the database to provide to a outside party. The table I want to search is to large to export to any format.

Is this possible?

  • What data type are the fields? – Mark Sinkinson May 7 '14 at 12:49
  • Character strings/varying. Its text. – Sarge May 7 '14 at 12:50
  • What if the resulting table is too large to export to any format? – Aaron Bertrand May 7 '14 at 12:57
  • The search is very limited. Its extremely unlikely for the results to be to large. – Sarge May 7 '14 at 12:59
  • I dont have the search already. I just saw your answer Will try it now. – Sarge May 7 '14 at 13:10

Your search:

  FROM dbo.existing_table
  WHERE col LIKE '%keyword1%'
     OR col LIKE '%keyword2%'

To put the results into a new table:

  INTO dbo.new_table_name
  FROM dbo.existing_table
  WHERE col LIKE '%keyword1%'
     OR col LIKE '%keyword2%'
| improve this answer | |
  • Thank you. That worked. In this case I only had to really search one column. However is it possible to search all columns in the same table without having to manually specify each? Right now I am having to replace the "col field". Also can this be applied database wide? (I will mark it as answer regardless shortly.) – Sarge May 7 '14 at 13:16
  • @Sarge No, there is no concise way to tell SQL Server to just search all columns (or all tables). That seems to be a different question - it would require dynamic SQL and better requirements. – Aaron Bertrand May 7 '14 at 13:17
  • 1
    @Sarge You want something like this – Mark Sinkinson May 7 '14 at 13:29
  • @AaronBertrand Noob question. Do I continue the rest of the query with OR. Or is WHERE OR interchange. – Sarge May 7 '14 at 13:54
  • @Sarge well, you're trying to match a bunch of keywords, right? You need an OR for each keyword. There are other ways to approach the problem (like a UNION ALL query for each keyword) but since I suspect this is a one-time operation this is probably sufficient. – Aaron Bertrand May 7 '14 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.