BCP and Function

Hi, I have 2 questions:

1. Is it possible that I can filter out the records and only load those that I wanted during BCP loading?

I am using SQL Server 2005 and doing the bulk copying of the data from a .dat file into a SQL table.

The BCP process is working fine, but one of the column in this .dat file indicate the type of data I am getting.

One is 'T' for text, the other is 'N' for numeric. So, is it possible that I can import the data of these 2 types

separately, meaning I want to load the 'N' type first, then 'T' after, but it does not have to go in that order.

Whichever type goes first is fine, but can I filter out like we could in SQL with the where clause?

2. Is it possible to have the value returns from a function as a list of items, instead of an individual item.

for example, in query, i can say I wanted a list of cities ....where city in(select city from sometable), but

i know function only allows me to return one value (New York) for example, not the whole list of cities within the US.

so, is this possible with a function or I can only do this with stored procedure?

i have both scalar and in-line/variable table function, but none of them give me what I need.

the scalar only returns 1 value, and the table function I would still only can put in specific parameter for it to return

a list. For instance, from using my table function, I have "select * from ::fn_test(ID, city)', this mean I still

can only put in one id and one city at a time with all the columns that I specified, not a list of ID and cities.

do I make any sense? please help/advise if you can, appreciated much.

1. Not really. Better methods include using SSIS -or loading into a work table, and then having a script or stored procedure handle the data in the work table, making whatever alterations/validations/cleanups are necessary before moving the data into the production table.

2. You can create a Table Valued Function (TVF) that will return a single column table (which is a list). I suspect that you are not completely clear about how you can use that TVF. Perhaps if you created a new post, and you provided more concete information, including the table DDL, sample data in the form of INSERT statements, and a clear explanation of your desired results, we may be better able to help you. (See this link for more information about how to prepare your submission.)