DECLARE @sql VARCHAR(5000)

SET @sql = 'DELETE tableName WHERE 1 = 1'

SELECT @sql = @sql + ' AND ' + name + ' IS NULL' from syscolumns where id = OBJECT_ID('tableName')

EXEC (@sql)

I forgot to mention this table has a primary key which is not null for sure. I don't want to do something like column1 is not null and column2 is not null .... columnN is not null. I think i can do this: do an unpivot by the primary key to create a virtual table having two columns (id, value), create a temp table. create table #temp (id) select into #temp(id) select id from unpivot_table group by column_id having sum(isnull(column_value,0))=0 if this is OK, i will delete any rows having the id in #temp. I am not familiar with unpivot so I am going over it. Will update this post soon.

本文发表在 rolia.net 枫下论坛In my distinct select, record 6 doesn't show. Hmmmmmmmmm.... That means anybody doesn't show up in the party will be...

CREATE TABLE #pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO #pvt VALUES (1,4,3,5,4,4)
INSERT INTO #pvt VALUES (2,4,1,5,5,5)
INSERT INTO #pvt VALUES (3,null,null,null,null,4)
INSERT INTO #pvt VALUES (4,4,2,null,null,4)
INSERT INTO #pvt VALUES (5,5,1,5,5,null)
INSERT INTO #pvt VALUES (6,null,null,null,null,null)
INSERT INTO #pvt VALUES (7,null,0,1,2,4)
GO

select * from #pvt

--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM #pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt
GO

select distinct VendorID
from
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM #pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt


select vendorid
from #pvt
group by vendorid
having sum(Emp1)=8

drop table #pvt更多精彩文章及讨论，请光临枫下论坛 rolia.net

DECLARE @SQL VARCHAR(5000)

SET @SQL = 'DELETE FROM YOUR_TABLE_NAME WHERE 1=1 '

SELECT @SQL = @SQL + ' AND ' + COLUMN_NAME + ' IS NULL ' from INFORMATION_SCHEMA.COLUMNS Where IS_NULLABLE = 'YES' and TABLE_NAME = YOUR_TABLE_NAME

EXEC (@SQL)

Following is the sample table. In this table, VendorId is primary key and all other columns are nullable. So, if I insert rows with following statements, I want to delete row 6 becasue it has all columns having null value. This sample table has 6 colums. The one I have does have about 150 columns.

CREATE TABLE #pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO #pvt VALUES (1,4,3,5,4,4)
INSERT INTO #pvt VALUES (2,4,1,5,5,5)
INSERT INTO #pvt VALUES (3,null,null,null,null,4)
INSERT INTO #pvt VALUES (4,4,2,null,null,4)
INSERT INTO #pvt VALUES (5,5,1,5,5,null)
INSERT INTO #pvt VALUES (6,null,null,null,null,null)
INSERT INTO #pvt VALUES (7,null,0,1,2,4)
GO

it generated a statement like:

delete from table_name
where column1 is null and column2 is null and ...and...

it worked, for sure. But the performance..., I need to do a test.

Thanks.

This table is a financing statement table storing all data collected from the whole world. It has 147 columns. One is primary key column, others are float type, nullable. I am loading all data in through several SSIS packages. It will take about 3 hours (at least) to update the whole database.

I will post the query performance result tomorrow. The query is created by bdbs(不多不少).

Thanks, everyone, especially to bdbs and have a good weekend.

On a desktop with dual-core 3.4G, 4G memory, Windows XP2, SQL 2005 with SP2. A table having 144 columns, 596000 rows of records, running this query

DECLARE @sql VARCHAR(max)
SET @sql = 'select count(*) from cf WHERE 1 = 1'
SELECT @sql = @sql + ' AND ' + name + ' IS NULL' FROM syscolumns WHERE id = OBJECT_ID('cf') AND IsNullable = 1
EXEC (@sql)

It took 15 seconds to finish. Row count is 0.