Deleting empty rows from table

Please can someone provide a T-SQL command that will delete empty rows from a
table
ThanksHuh? A row cannot exist if it is empty.
By definition a row is made up of data.
Perhaps you want to delete all rows where a specific column is empty (NULL).
If that is the case this should get you started:
/*
Identify the rows that you are about to delete
*/
SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
/*
Delete the rows
DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
*/
--
Keith
"airforce1" <airforce1@.discussions.microsoft.com> wrote in message
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks|||Hi,Keith
I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> Huh? A row cannot exist if it is empty.
> By definition a row is made up of data.
> Perhaps you want to delete all rows where a specific column is empty
(NULL).
> If that is the case this should get you started:
> /*
> Identify the rows that you are about to delete
> */
> SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
NULL
> /*
> Delete the rows
> DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
> */
> --
> Keith
>
> "airforce1" <airforce1@.discussions.microsoft.com> wrote in message
> news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> > Please can someone provide a T-SQL command that will delete empty rows
> from a
> > table
> >
> > Thanks
>|||The original poster did not post a very well written question, so it is
difficult to provide a good answer.
<quote>
Please can someone provide a T-SQL command that will delete empty rows from
a
table
Thanks
</quote>
--
Keith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%239Md5vu1EHA.804@.TK2MSFTNGP12.phx.gbl...
> Hi,Keith
> I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
>
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> > Huh? A row cannot exist if it is empty.
> > By definition a row is made up of data.
> >
> > Perhaps you want to delete all rows where a specific column is empty
> (NULL).
> >
> > If that is the case this should get you started:
> >
> > /*
> > Identify the rows that you are about to delete
> > */
> > SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
> NULL
> >
> > /*
> > Delete the rows
> > DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
NULL
> > */
> >
> > --
> > Keith
> >
> >
> > "airforce1" <airforce1@.discussions.microsoft.com> wrote in message
> > news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> > > Please can someone provide a T-SQL command that will delete empty rows
> > from a
> > > table
> > >
> > > Thanks
> >
>|||I suppose you can identify rows you want to delete. Maybe you want to delete
*identical* rows which can't be deleted though an error message (because the
database can't delete identical rows).
If it's the problem you are asking for, so add an autoincrement field in
your table and then repeat rows deletion.
In other case specify details ...
Vlastik
"airforce1" <airforce1@.discussions.microsoft.com> pí¹e v diskusním pøíspìvku
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thankssql

Deleting empty rows from table

Please can someone provide a T-SQL command that will delete empty rows from a
table
Thanks
Huh? A row cannot exist if it is empty.
By definition a row is made up of data.
Perhaps you want to delete all rows where a specific column is empty (NULL).
If that is the case this should get you started:
/*
Identify the rows that you are about to delete
*/
SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
/*
Delete the rows
DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
*/
Keith
"airforce1" <airforce1@.discussions.microsoft.com> wrote in message
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks
|||Hi,Keith
I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> Huh? A row cannot exist if it is empty.
> By definition a row is made up of data.
> Perhaps you want to delete all rows where a specific column is empty
(NULL).
> If that is the case this should get you started:
> /*
> Identify the rows that you are about to delete
> */
> SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
NULL
> /*
> Delete the rows
> DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
> */
> --
> Keith
>
> "airforce1" <airforce1@.discussions.microsoft.com> wrote in message
> news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> from a
>
|||The original poster did not post a very well written question, so it is
difficult to provide a good answer.
<quote>
Please can someone provide a T-SQL command that will delete empty rows from
a
table
Thanks
</quote>
Keith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%239Md5vu1EHA.804@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hi,Keith
> I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
>
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> (NULL).
> NULL
NULL
>
|||I suppose you can identify rows you want to delete. Maybe you want to delete
*identical* rows which can't be deleted though an error message (because the
database can't delete identical rows).
If it's the problem you are asking for, so add an autoincrement field in
your table and then repeat rows deletion.
In other case specify details ...
Vlastik
"airforce1" <airforce1@.discussions.microsoft.com> pe v diskusnm pspvku
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks

Deleting empty rows from table

Please can someone provide a T-SQL command that will delete empty rows from
a
table
ThanksHuh? A row cannot exist if it is empty.
By definition a row is made up of data.
Perhaps you want to delete all rows where a specific column is empty (NULL).
If that is the case this should get you started:
/*
Identify the rows that you are about to delete
*/
SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
/*
Delete the rows
DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
*/
Keith
"airforce1" <airforce1@.discussions.microsoft.com> wrote in message
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks|||Hi,Keith
I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> Huh? A row cannot exist if it is empty.
> By definition a row is made up of data.
> Perhaps you want to delete all rows where a specific column is empty
(NULL).
> If that is the case this should get you started:
> /*
> Identify the rows that you are about to delete
> */
> SELECT * FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS
NULL
> /*
> Delete the rows
> DELETE FROM YourTableNameGoesHere WHERE TheEmptyColumnNameGoesHere IS NULL
> */
> --
> Keith
>
> "airforce1" <airforce1@.discussions.microsoft.com> wrote in message
> news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> from a
>|||The original poster did not post a very well written question, so it is
difficult to provide a good answer.
<quote>
Please can someone provide a T-SQL command that will delete empty rows from
a
table
Thanks
</quote>
Keith
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%239Md5vu1EHA.804@.TK2MSFTNGP12.phx.gbl...
> Hi,Keith
> I think the OP was mentioned to delete the rows which are =' ' ,wasn't he?
>
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u6Hpqkt1EHA.304@.TK2MSFTNGP11.phx.gbl...
> (NULL).
> NULL
NULL[vbcol=seagreen]
>|||I suppose you can identify rows you want to delete. Maybe you want to delete
*identical* rows which can't be deleted though an error message (because the
database can't delete identical rows).
If it's the problem you are asking for, so add an autoincrement field in
your table and then repeat rows deletion.
In other case specify details ...
Vlastik
"airforce1" <airforce1@.discussions.microsoft.com> pe v diskusnm pspvku
news:AC7D6FEF-0743-4EE6-AD8B-F5DB008AF36D@.microsoft.com...
> Please can someone provide a T-SQL command that will delete empty rows
from a
> table
> Thanks

Deleting a UDF that does not exist...

I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
T-SQL command in a script:
GRANT EXEC on xxxyyyzzz to harry
problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
highlight this or any user on this database and try to click the Permissions
button, I get error:
--
Microsoft SQL-DMO
--
Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
UserDefinedFunctions collection. If the name is a qualified name, use [] to
separate various parts of the name, and try again.
how can I delete this "phantom" object? If I scroll through all the objects
that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
it.
Please help. Thanks.OK. Located it in the systemobjects table, but when I try to delet that row,
get an error message:
Ad hoc updates to the system catalogs are not enabled. The system
administrator must reconfigure SQL server to allow this.
Help again.
"Sam" wrote:
> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.|||Never mind - was able to delete it from the sysobjects table using:
sp_configure 'allow updates; ,1
reconfigure with overrride
all's well that ends well. no more error message
"Sam" wrote:
> I tried to grant a user (harry) rights to a UDF (xxxyyyzzz) by using this
> T-SQL command in a script:
> GRANT EXEC on xxxyyyzzz to harry
> problem is, there was no xxxyyyzzz UDF or SP in the database. Now when I
> highlight this or any user on this database and try to click the Permissions
> button, I get error:
> --
> Microsoft SQL-DMO
> --
> Error 21776: [SQL-DMO]The name 'xxxyyyzzz' was not found in the
> UserDefinedFunctions collection. If the name is a qualified name, use [] to
> separate various parts of the name, and try again.
> how can I delete this "phantom" object? If I scroll through all the objects
> that this user has rights to, I can see the xxxyyyzzz UDF, but I can't delete
> it.
> Please help. Thanks.

Delete with Updategrams

Hi,
Is it posible to delete all records from table (like T-SQL: Delete From
MyTable) using XML Updategrams ?
Thanks,
Marcin
Updategrams aren't meant for batch operations like that. To do it you would
first have to select all of the data out and put it into the "before" block,
and then omit the "after".
Irwin
"Marcin" <mwieckow@.onet.pl> wrote in message
news:ui6sGWVKFHA.2396@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it posible to delete all records from table (like T-SQL: Delete From
> MyTable) using XML Updategrams ?
> Thanks,
> Marcin
>

Delete with Updategrams

Hi,
Is it posible to delete all records from table (like T-SQL: Delete From
MyTable) using XML Updategrams ?
Thanks,
MarcinUpdategrams aren't meant for batch operations like that. To do it you would
first have to select all of the data out and put it into the "before" block,
and then omit the "after".
Irwin
"Marcin" <mwieckow@.onet.pl> wrote in message
news:ui6sGWVKFHA.2396@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Is it posible to delete all records from table (like T-SQL: Delete From
> MyTable) using XML Updategrams ?
> Thanks,
> Marcin
>

DELETE TOP

Environment: SQL Server 2005
Language: T-SQL

I want to insert the top first record from the temp table '#NewTemp'
to the Employee table 1 record at a time until all the records from the temp table
have been copied.
After inserting the top first record into the Employee table, how can I retrive the identity key 'NewEmployeeID' from the Employee table?
I would then like to delete the top row in the #NewTemp table.
The process should be repeated until the @.Counter variable is 0.
Currently, I get an error msg 'Incorrect syntax near the keyword TOP'.What's wrong with the following T-SQL and how can I fix it? thanks.

BEGIN
DECLARE @.Counter as INT
SELECT @.Counter = COUNT(*) FROM #NewTemp
DECLARE @.TopCount AS INT
SET @.TopCount = 1

WHILE @.Counter > 0
BEGIN
INSERT INTO dbo.Employee(
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail)
SELECT TOP(@.TopCount)
EmployeeFName
, EmployeeLName
, EmployeePhone
, EmployeeEmail
FROM dbo.#NewTemp

SELECT SCOPE_IDENTITY() AS NewEmployeeID

PRINT 'The count is ' + CONVERT (VARCHAR (10), @.Counter)

PRINT --NewEmployeeID

SET @.Counter = @.Counter -1

DELETE TOP(@.TopCount) from dbo.#NewTemp
END

DROP TABLE dbo.#NewTemp
END

I'm sure you have your reasons, but I have to say, this is very, very 'odd' indeed.

DELETE does NOT recognize TOP. You cannot [ DELETE TOP ]. In fact, if users are active in your database, a second query using TOP may not return the same row(s) as the first query. In your SELECT query, TOP without a WHERE criteria just doesn't make any sense.

Why go to so much trouble?

Let us know what you are attempting to accomplish and perhaps we can help you.

|||

What I'm trying to accomplish is this.

Copy each row from temp table to the Employee table. After inserting each record into the Employee table,

I would like to get the Identity key for the new record inserted from the Employee table. Thanks.

|||

You will need to use the new OUTPUT clause of the input statement. You cannot use either @.@.identity or scope_identity() when you are inserting multiple rows. Just a second and I will try to get you an example. Give a look to this article:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

You ought to be able to do something like this to retrieve your list of identities:

create table #identityList (idKey integer)

insert into targetTable
select col1,
col2,
...
colN
output inserted.theIdentityColumn
into #identityList
from yourTempTable

-- to retrieve the identity values:
select idKey

from #identityList

Now, if you are going to need to match your identies back to the temp table that you inserted from you will also need to include column(s) in your identity list that make the inserted row unique.

|||

thanks, nice article. I think it will help. Is 'inserted' a reserved word in the code above?

In my code above, reason I have while loop and a counter is so I can go each record at time for insertion from the

temp table to the Employee table. What would be the alternative if using scope_identity() or @.@.identity?

|||

John,

In my earlier response, I asked if you would give us more detail information about what you are attempting to accomplish. It is not at all clear what is going on here.

If you only wanted to move the data from the #NewTemp table into the Employees table, it is as simple as an INSERT... SELECT.

Code Snippet

INSERT INTO dbo.Employee
( EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
)
SELECT
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
FROM dbo.#NewTemp

However, perhaps there is some thing you still haven't told us. What is your concern about the SCOPE_IDENTITY() or @.@.IDENTITY?

We can't do a good job of helping you unless you give us all of the information.

|||

create table dbo.#newTemp
( EmployeeFName varchar(20),
EmployeeLName varchar(20),
EmployeePhone varchar(14),
EmployeeEmail varchar(30)
)
insert into dbo.#newTemp
select 'Dave', 'Mugambo', '(800)555-1212', null union all
select 'Kent', 'Waldrop', '(800)555-1212', null union all
select 'Geoff', 'Waldmeyer', '(800)555-1212', null
select * from dbo.#newTemp
/*
EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- --
Dave Mugambo (800)555-1212 NULL
Kent Waldrop (800)555-1212 NULL
Geoff Waldmeyer (800)555-1212 NULL
*/

declare @.idList table (newEmployeeID int)

insert into dbo.Employee
output inserted.employeeId
into @.idList
select EmployeeFName,
EmployeeLname,
EmployeePhone,
EmployeeEmail
from dbo.#newTemp

-- --
-- If you need to see the new employee
-- information, you can you the
-- permanent table to the IDList like
-- this:
-- --

select EmployeeId,
EmployeeFName,
EmployeeLName,
EmployeePhone,
EmployeeEmail
from dbo.employee a
inner join @.idList b
on a.employeeId = b.newEmployeeId

/*
EmployeeId EmployeeFName EmployeeLName EmployeePhone EmployeeEmail
-- -- -
1 Dave Mugambo (800)555-1212 NULL
2 Kent Waldrop (800)555-1212 NULL
3 Geoff Waldmeyer (800)555-1212 NULL
*/

go

drop table dbo.#newTemp

(edited the syntax; it was previously wrong)

|||

After filling the table variable with the inserted records,

how can I get each newEmployeeId one at a time. I would like to

pass this newEmployeeID to another sp one at a time.

thanks much.

|||

If this is what you must do, then use a cursor with a while loop -- but understand, cursors tend to be performance killers. A better approach is to design set based operations; it is what SQL server is best at. I will edit this with some posts that talk about the problems of cursors.

Here are some posts that comment on cursors:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1372104&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=892822&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=447559&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=437891&SiteID=1 http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=588762&SiteID=1

I have many more examples in my list.

|||

If you really MUST do a row-wise operation, instead of a CURSOR, I recommend using the previous suggestion of the SQL 2005 OUTPUT keyword, outputing into a table variable, then adding an IDENTITY column to the table variable, and then using a WHILE loop to step through the rows in the table variable.

It will not be as resource intensive on the server, and most likely even execute faster than a CURSOR.

|||

As an aside, delete does recognize TOP in 2005. It is non ordered, so it doesn't make sense in this particular case. It's use is to batch deletes, like if you want to delete a large number of rows, but want to limit the pressure on the log, you can just delete them in batches over and over until all data is gone.

Code Snippet

create table #NewTemp
(
EmployeeFName varchar(30)
, EmployeeLName varchar(30)
, EmployeePhone varchar(30)
, EmployeeEmail varchar(30)
)

insert into #newTemp
select 'fred','flintstone','quarry 3424','fredf@.slate.com'
union
select 'barney','rubble','quarry 3452','barneyr@.slate.com'

delete top (1) from #newTemp

select *
from #newTemp