Need Query to get first 147 fields 3

[srogers]

Is there a way to do a select statement to get the first 147 fields of a table (and then another one for the second 147 fields) without typing all the field names?

I'm importing them into Excel with DTS and can't do it all at once since Excel limits me to 256 columns. It would also be ok if I could get the DTS to collect the first 256 columns and then I could get the rest.

Or maybe there's a way to exclude fields with certain characters in the field name (92 in my case) to lower the number of fields I get.

Thank you.

 

[donutman]

<<without typing all the field names>>
Well, you can let the view designer in EM type them all, then cut and paste. Design your view as
Select * From MyTable
Then run the view. The view designer will rewrite the query.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrees]

Try:

SELECT 
	 b.[name]
	,colid 
FROM
  sysobjects a 
JOIN
  syscolumns b on a.[id] = b.[id]
WHERE
	a.[name] = 'MyTableName' AND colid between 1 AND 147
ORDER BY colid

You can then just alter the BETWEEN clause as needs be to get other columns.

mrees

 

[ESquared]

Why does a table ever need to have 294 columns?

 

[SQLSister]

Esquared I was wondering that myself. What worries me about that number of columns is that it is easyto run up against the record size limit. Sure it will let you create a structure that potentially will have over 8060, but it won't let you insert data into it if a particular record goes over that amount. So you may not even realize you have a problem until an unually long record goes in.

Also, often when there are that number of columns people don't truly understand relational tables and have fields like Item1, Item2, Item3 etc.

Questions about posting. See faq183-874

 

[srogers]

Thanks a bunch Karl and mrees.

ESquared and SQLSister - I know - that IS a lot of fields. I didn't even realize it at first until Excel had a problem with it. Then I took a closer look to see how many fields were there. I didn't create these tables though, it's a purchased package. And I don't think there are many like that one table.

I don't have a lot of experience with this yet.
SQLSister - would you mind elaborating on the record size limit? Do you mean the number of records is limited or is there some other size limitation on one record by itself?

Thanks for your comments -
Sharon

 

[SQLSister]

There is a limit on the number of bytes a record can have. Since varchar and nvarchar fields can store less than their maximum limit, SQL server will allow you to create a table where the maximum value stored in each field would be longer than the total limit per record. It however will not let you insert a record that actaully exceeds the limit. So you may go along fine for years until suddenly you have record that exceeds the limit and you can't get it to store in the database.

Usually it is a good idea to not create a table which will ever be larger than the record limit of 8060 bytes unless you are positive that all the fields will never be filled in. It is better to create a related table for the additional fields with a one to one relationship to the additional table. Then you don't have anything mysteriously breaking for no apparent reason.

All too many commercial packages have poor data structures.

Questions about posting. See faq183-874

 

[ESquared]

...a record with an average of just 28 bytes in each of the 294 fields will exceed 2080 bytes.