Query across all colums

Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?
Thanks
Shabassanot really|||Ok, thanks was worth a question.|||If you are comparing all the columns in one table to all the columns in another, you may be able to use the CHECKSUM or BINARYCHECKSUM functions:

select subA.APKey, sbuB.BPKey
from
(select A.PKey as APKey, CHECKSUM(*) as ChecksumA from A) subA
full outer join
(select B.PKey as BPKey, CHECKSUM(*) as ChecksumB from B) subB
on subA.ChecksumA = subB.ChecksumB
where ...|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:|||While Rudy is quite right in terms of how SQL itself does things, there are extensions to many database engines that make this kind of search easier. In MS-SQL, this is called Full Text Indexing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_5dro.asp).

-PatP|||pat, all things considered, full text indexing is not "simpler" (one of shabassa's original requirements)

:)|||From the standpoint of managing the server, you are quite right. Full-Text Indexing brings its own problems to the table.

From the standpoint of writing a search query though, I feel that it is simpler and I don't think anyone would argue it is much less code.

-PatP|||blindman, i think the problem was to write this --

... WHERE a LIKE '%x%' OR b LIKE '%x%' OR c LIKE '%x%' OR d LIKE '%x%'

in some easier fashion, e.g.

... WHERE allcolumns LIKE '%x%'

and the answer, of course, is "not really" :cool:

Could be. Without a doubt he needs to be more specific.|||I doubt it though....

I think they're looking for duplicates...

Or they're just trying to establish a join between to related tables, just in the keys?|||Or attempting to create a search feature for their application that searches against a catalog of objects where the entry could match a value in any number of fields (Ex: Name, Description, Price, etc).|||Step right up, folks! Play "Guess the user requirements" and win a prize for the little lady! Who's next? Every player has a chance to win! You sir, yes you...!|||Well if I was programming an application that searched across the table there would be no problem doing it in some kind of loop. I was just lazy and wanted to look for a certain row where I entered a date in an application accessing that db. But I didn't know to which colum the text field in the app corresponded to. So I wanted to search across all columns at once using the Enterprise Manager.
I hope that clears things up. ;)

How would this be possible with full text indexing? Do I magically get some kind of search field?

Shabassa

Query

Hello folks,

Im new in database bussines. I will appreciate if someone can help me.
I need to insert new records to a table. First of all I dont know what is the syntax for insert command in SQL Server2000. Also one of the fields to insert is changing every second so I think I will need to include in the insert command a nested select statement.
For example,

The table has the ID field and this field is the one is changing every time. If in the n second I do a select to know what value such field has I could use the value equal to ID + 1. But while I build query, type and execute it will be the second n+1, so probably the value for ID now is already equal to ID + 1 and my query will fail because the duplicated record error.

I hope is clear what the proble I have,

Regards,You can look in BooksOnline (comes with the sql server) for the syntax on insert statements.
If your changing field is the ID for a record, you can use auto numbering on this field. This means that sql server itself will keep track of the value of the field and makes sure there are no duplicates.

hth|||So can I just use:

insert into table_name values(ID,'MNAME','F_NAME')...

I was thinking that some other query was needed to be introduced to get the new value for ID field, ie.

insert into table_name values(((select ID from TABLE)+1),'M_NAME','F_NAME')

Thanks|||Not completely correct yet, your insert statement. If the ID column is set to auto numbering, you don't include it in the insert statement. Your statement will be:
insert into table_name values('MNAME','F_NAME')|||Thanks, How I can know if the field is autonumbering?

How I can get the table fields with all it properties using SQL Command?

In db2 it was with command describe table...
It is the same?

Regards,|||in sql server, there is a stored proeedure for this information called 'sp_columns'. See BOL for more information. You can also find information about using auto numbering in BOL, just search on 'identity'.sql