(Urgent) Help need with in selecting too many columns.

Hi,

I have a dbf file and that file has around 154 columns and in that i want to pull just 88 columns to my sql server database... I am using a OLedb connection and a data reader to read the data from the DBF file and then using a sqlbulcopy to insert the data into SQL server 2005 database. I have created a destination table for the 88 columns. This is my select statement for the dbf file. I have also used a Rownumber which is Int identity so i am using a 0 in the first column.

Dim commandAs Data.OleDb.OleDbCommand =New Data.OleDb.OleDbCommand("Select 0,* from FUND.DBF", connection)

Now my question is Since i want to pull 88 columns instead of 154 column, I was thinking to give a select statement like

("Select, 0,Column1,....Column88 From Fund.DBF", connection)

SO instead of doing this is there is a way that i can specify in the sql statement that will tell it not to pull the rows after the 88th column.

Any help will be appreciated.

Regards,

Karen

I know of a way to do it but it isn't elegant

SQL stores metadata about its data in system tables. You could check the column meta-data table for all columns of a certain table, then write a cursor to build a dynamic sql select for the first 88 rows and execute the statement

If you are using SQL Server 2005, here is the select statement to pull all columns for a specific table

select *from sys.columnsjoin sys.tableson sys.Columns.object_id = sys.tables.object_idand sys.tables.[name] ='TableName'

|||

But i need to ignore the data after Column 88 in a dbf file... is it possible to do it? if not i can write a select statement to just select the fields i want.

|||

Yes, if you did what I said above you wouldn't have to write out all 88 columns. You would write a cursor that would loop through all the columns in the sys.Columns table while the count is less than or equal to 88

|||

Unfortunately, the only ways I can think of doing so requires naming all the columns that you do want. Of course that depends on the system you are pulling the data from, but with standard sql, there is no such way at least not one that is universal across all SQL implementations.

You can however, make an initial request to the datasource, pulling back 0 rows (SELECT * FROM ... WHERE 1=0) into a dataset. Then you iterate through the first 88 columns of the empty dataset to get the column names, and build up a select command. Of course this is slower, and has more overhead. It is probably easier, faster, and more efficient just to name all 88 columns.