Removing NULL rows from table x with # of columns x
-
I'm importing data in from source with NULL rows. (no source can't be modified to get rid of these) After I've staged this data into SQL, I want to identify these NULL rows and remove them. I don't want to hard code where col1 is null and col2 is null and col3 is null etc.....
I'm playing around with passing the tablename in as a variable and then interfacing with syscolumns and sysobjects to identify all columns in said table. If ALL of these columns are NULL, i then want to remove the entry.
I'm so close (yet so far), my brain hurts. Anybody already done this?
JSTANGE -
You do not need to remove anything.
Just don't copy it from staging to destination.
Add WHERE clause to the last INSERT statement.
_____________
Code for TallyGenerator -
The WHERE clause would have to inspect col1 is null, col2 is null, col3 is null etc...
I could have 100 columns. This is why i'm trying to you colid 1-whatever in syscolumns (or something)
JSTANGE -
Do you insert that staging dataset into any actual database after all?
_____________
Code for TallyGenerator -
I am intending on posting the contents of staging table 1 into final table 1 w/out the NULL rows.
JSTANGE -
Do you have a SQL statement for this?
Can you add WHERE .. IS NOT NULL to this?
_____________
Code for TallyGenerator -
"If ALL of these columns are NULL, i then want to remove the entry."
Do you want to remove columns where all columns are null, or just 5 out of 10 cols are null, or all but the pk/fk, or...
-
i want to remove records where all fields are NULL
JSTANGE -
the WHERE .. IS NOT NULL option is my problem.
I don't want to type WHERE col1 IS NOT NULL and col2 IS NOT NULL and col3 IS NOT NULL.......and col100 IS NOT NULL
JSTANGE -
Do you have in this statement
SELECT col1, col2, col3,....... col100 ?
_____________
Code for TallyGenerator -
here is where my heads at....
select * from syscolumns where id = (select id from sysobjects where name = <tablename>
This will provide a list of all columns in a given table. It's almost like I want to setup an array and when all array elements are NULL, then delete that record.
JSTANGE -
I think this pretty much does it, but I'm still getting an error when I try to execute the SP. A little tweaking by someone should fix it.
CREATE PROCEDURE dbo.No_More_Nulls (@v_Table_Name varchar)
AS
BEGIN
DECLARE @v_NextCol varchar(50), @v_Delete_String nvarchar, @v_Num_Cols int,
@v_Count_Cols int
DECLARE c_AllCols CURSOR LOCAL
FOR
SELECT sc.name
FROM syscolumns sc, sysobjects so
WHERE so.name = @v_Table_Name
and so.id = sc.id
SELECT @v_Num_Cols = COUNT(sc.name)
FROM syscolumns sc, sysobjects so
WHERE so.Name = @v_Table_Name
and so.id = sc.id
SET @v_Count_Cols = 0
SET @v_Delete_String = 'Delete From ' + @v_Table_Name + 'WHERE '
OPEN c_AllCols
FETCH NEXT FROM c_AllCols
INTO @v_NextCol
WHILE @@FETCH_STATUS = 0
BEGIN
SET @v_Count_Cols = @v_Count_Cols + 1
IF @v_Count_Cols = @v_Num_Cols
BEGIN
SET @v_Delete_String = @v_Delete_String +
@v_NextCol + ' IS NULL AND'
END
ELSE
BEGIN
SET @v_Delete_String = @v_Delete_String +
@v_NextCol + ' IS NULL'
END
FETCH NEXT FROM c_AllCols
INTO @v_NextCol
END
EXECUTE sp_ExecuteSQL @v_Delete_String
END
Good luck
-
Or try this:
declare @tablename varchar(100)
select @tablename = 'yourtable'
declare @sql varchar(8000)
select @sql = 'delete ' + @tablename + ' where '
select @sql = @sql + COLUMN_NAME + ' is null and ' from information_schema.columns where TABLE_NAME = @tablename
order by ORDINAL_POSITION
select substring(@sql, 1, len(@sql) - 4)
-- exec(substring(@sql, 1, len(@sql) - 4))
-
The "Or try this" code worked great.
Thanks ALL for the input, much obliged.
JSTANGE -
Joe's solution of using COALESCE is what I first thought of myself.
Then I got to thinking about whether it would be possible to use checksum.
Insert a row in the table with all the columns null and find the checksum value
SELECT CHECKSUM(*) FROM
Then delete the unwanted records
DELETE FROM
WHERE CHECKSUM(*) = ?
substitue ? with the checksum above
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply