How to clean (empty) a table?

Hi:
I have a table in a database, how to clean this table (remove all data
rows)? I guess there should be some SQL command to do that? There are
millions of rows, kind of hard to empty the table by hand.
Thanks for your helpDELETE tablename
or
TRUNCATE TABLE tablename
The latter is handy if there are no PK/FK relationships that it would break,
because (a) it is lightly logged, and (b) it also resets the seed for an
IDENTITY column, if one exists.
"Polaris" <etpolaris@.hotmail.com> wrote in message
news:#2EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> Hi:
> I have a table in a database, how to clean this table (remove all data
> rows)? I guess there should be some SQL command to do that? There are
> millions of rows, kind of hard to empty the table by hand.
> Thanks for your help
>|||Thanks guys for your quick help!
Polaris <etpolaris@.hotmail.com> wrote in message
news:#2EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> Hi:
> I have a table in a database, how to clean this table (remove all data
> rows)? I guess there should be some SQL command to do that? There are
> millions of rows, kind of hard to empty the table by hand.
> Thanks for your help
>|||Slight correction:
The table you try to TRUNCATE can't be referenced by any Foreign Keys, but
it can reference other tables and it can have a primary key.
Also, permissions on TRUNCATE TABLE are limited to the sysadmin, db_owner
and ddl_admin roles, which probably makes it a poor solution in production
systems.
Jacco
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:eApNZzwRDHA.2252@.TK2MSFTNGP12.phx.gbl...
> Polaris,
> TRUNCATE TABLE <tablename> is a good option if the tables doesnt have any
> PK/FK's.This is minimally logged and since you are dealing with millions
of
> rows, this means a lot.If you cant use TRUNCATE TABLE, then the only
option
> left would be to do DELETE <tablename>.But here, do the DELETE in small
> batches and then truncate the transaction log in between so that you dont
> end up with a big log .
>
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "Polaris" <etpolaris@.hotmail.com> wrote in message
> news:%232EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> > Hi:
> >
> > I have a table in a database, how to clean this table (remove all data
> > rows)? I guess there should be some SQL command to do that? There are
> > millions of rows, kind of hard to empty the table by hand.
> >
> > Thanks for your help
> >
> >
>|||Hi all!
I am in need of writing a few stored procedures.
The first one is to create a stored procedure to recover a database from
backup and the second one is to create a stored procedure to execute a
transaction log backup (even though I know this can be done through a
maintainence plan). Any help would be greatly appreciated.
Thanks in advance!|||Jacco,
Thanks for the correction! My intention was same as you mentioned but my
wording was wrong.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23hraMMxRDHA.2196@.TK2MSFTNGP11.phx.gbl...
> Slight correction:
> The table you try to TRUNCATE can't be referenced by any Foreign Keys, but
> it can reference other tables and it can have a primary key.
> Also, permissions on TRUNCATE TABLE are limited to the sysadmin, db_owner
> and ddl_admin roles, which probably makes it a poor solution in production
> systems.
> Jacco
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:eApNZzwRDHA.2252@.TK2MSFTNGP12.phx.gbl...
> > Polaris,
> >
> > TRUNCATE TABLE <tablename> is a good option if the tables doesnt have
any
> > PK/FK's.This is minimally logged and since you are dealing with millions
> of
> > rows, this means a lot.If you cant use TRUNCATE TABLE, then the only
> option
> > left would be to do DELETE <tablename>.But here, do the DELETE in small
> > batches and then truncate the transaction log in between so that you
dont
> > end up with a big log .
> >
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "Polaris" <etpolaris@.hotmail.com> wrote in message
> > news:%232EfxmwRDHA.1720@.TK2MSFTNGP12.phx.gbl...
> > > Hi:
> > >
> > > I have a table in a database, how to clean this table (remove all data
> > > rows)? I guess there should be some SQL command to do that? There are
> > > millions of rows, kind of hard to empty the table by hand.
> > >
> > > Thanks for your help
> > >
> > >
> >
> >
>