Using 'SELECT' statement and multiple filters?

[JonnyCab]

Hi,

I have a table with project costs and each cost (record) has a resource code. I want to pull out all costs, where the resource code matches any one of eight resource codes specified. Then move those matching records to a different table.

ie

SELECT * from tbl WHERE RCode = "CHR" or "TRD" or ....(X8 Resource Codes)

Then do the same for records using another set of resource codes and transfer these to a different table again.

What is the best way, or any way, of doing this. Any code example would be appreciated.I am using VB6 and ADO.

Cheers

[gerard]

One way I thought would be to use a make table select statement using the in clause... rcode in ("RFT", "TRN",)
and just use the execute statement to action the query in ADO.

Gerard

[RIVES]

Here's the thought.


Do your target tables already exist? Are you using SQL Server or MS Access.

Anyway,

SQL Server:

IF your target table does not exist yet, you could SQL Server could make it automatically for you. You just have to check the SELECT INTO/BULK Copy in the dp options. Right click on the database and then properties...or, if you want the hard way, use the sp_dboptions system stored procedure.

Insert into <TARGET TABLE> Select column1, column2, columnn from tbl WHERE RCode IN ("CHR", "TRD",....X8 Resource Codes)