I have data on MS SQL Server that have over 60 columns, I would like to
select 30 of these columns that may or may not contain NULL and I don't
want to write out all 30 columns and check for IN NOT NULL. Does anyone
know how to do that?Not trying to be harsh, but... start typing. :)
Is this a recurring problem, or are you shooting for a one time
solution? If one-time, the easiest way to do it is to just write the
SQL query that you're attempting to avoid. If you're trying to develop
some sort of administrative tool to help you validate data, you could
script something that uses dynamic SQL (using the syscolumns table).
That's not something that I would advocate giving to the average user.
Stu
timhz...@.gmail.com wrote:
> I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?|||You must perform each IS NOT NULL test in the query. However, you can
save some typing. This will generate a query to get you started, just
copy the results.
declare @.tbl varchar(50)
set @.tbl = 'Categories'
SELECT CASE WHEN C.ORDINAL_POSITION =
(select min(ORDINAL_POSITION)
from INFORMATION_SCHEMA.COLUMNS C2
where C2.TABLE_NAME = @.tbl
and C2.IS_NULLABLE = 'YES')
THEN 'SELECT * FROM ' + C.TABLE_NAME +
char(13) + CHAR(10) +
' WHERE '
ELSE ' AND '
END +
C.COLUMN_NAME + 'IS NOT NULL' + char(13) + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME = @.tbl
AND C.IS_NULLABLE = 'YES'
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION
On 29 Jun 2006 08:48:16 -0700, timhzhou@.gmail.com wrote:
>I have data on MS SQL Server that have over 60 columns, I would like to
>select 30 of these columns that may or may not contain NULL and I don't
>want to write out all 30 columns and check for IN NOT NULL. Does anyone
>know how to do that?|||No, there are no lazy shortcuts in T-SQL to select "a set of columns".
But, there are lazy shortcuts to generating the list so that you can create
a valid and reasonable T-SQL statement more quickly. What are you using,
6.5, 7.0, 2000, 2005? In Query Analyzer or Management Studio, when you
expand a table and you see a folder called columns, drag it to the query
window. Voila, like magic, huh?
Laziness is not enough of a reason to use SELECT * (or s
a similar
alternative).
A
<timhzhou@.gmail.com> wrote in message
news:1151596096.534039.98000@.p79g2000cwp.googlegroups.com...
>I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?
>|||before you begin anything .. you might want to consider clean up the
data to update those columnes with a default value.
ie.
update table
set column = ''
where column is null
possibly put them in a temporary table?
timhzhou@.gmail.com wrote:
> I have data on MS SQL Server that have over 60 columns, I would like to
> select 30 of these columns that may or may not contain NULL and I don't
> want to write out all 30 columns and check for IN NOT NULL. Does anyone
> know how to do that?|||Contrary to Developer opinion, sometimes NULL is an appropriate value.
(Albieit, not as often as it is used just because folks don't take time to
understand the implications.)
A question I posit is: How many responses are there to a Yes/No question?
The correct response is Four. Yes, No, Not Answered, Not Applicable.
The use of the data determines whether or not there is a distinction between
the last two. For example, if I'm analyzing survey results, I wouldn't want
to confound the analysis by combining Not Answered and Not Applicable.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"BurgerKING" <syi916@.gmail.com> wrote in message
news:1151597871.024544.185460@.b68g2000cwa.googlegroups.com...
> before you begin anything .. you might want to consider clean up the
> data to update those columnes with a default value.
> ie.
> update table
> set column = ''
> where column is null
> possibly put them in a temporary table?
> timhzhou@.gmail.com wrote:
>
