Bulk Delete on SQL Server 2008 (Is There Anything Like Bulk Copy (Bcp) for Delete Data)

Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)

No.

You want a DELETE with a WHERE clause: this is standard SQL.

What you can do is batch deletes like this:

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
    DELETE TOP (xxx) MyTable WHERE ...

Or if you want to remove a very high percentage of rows...

SELECT col1, col2, ... INTO #Holdingtable
           FROM MyTable WHERE ..opposite condition..
TRUNCATE TABLE MyTable
INSERT MyTable (col1, col2, ...)
           SELECT col1, col2, ... FROM #Holdingtable

What is the difference between bulk copy (bcp) and bulk insert in SQL Server 2008?

• bulk copy is an utility program: bcp.exe
• BULK INSERT is a Transact-SQL statement.

bcp.exe uses BULK INSERT to do its job. It's the same relation that the one between sqlcmd.exe (a tool) and SELECT (a statement).

There is no 'switch' to export all tables in a database. There is however an export and import wizard which is based on SSIS, not on bcp.

How to delete the top 1000 rows from a table using Sql Server 2008?

The code you tried is in fact two statements. A DELETE followed by a SELECT.

You don't define TOP as ordered by what.

For a specific ordering criteria deleting from a CTE or similar table expression is the most efficient way.

;WITH CTE AS
(
SELECT TOP 1000 *
FROM [mytab]
ORDER BY a1
)
DELETE FROM CTE

Removal of 30-40 millions record without affecting performance

You have to use small chunks otherwise your transaction log will increases

Every one one of 30-40 million deletes will be logged. If you create a new table and copy "to keep" rows you'll still have 50+ million logged rows. The fact of simple vs full recovery doesn't matter: each delete/insert is logged

If the log increases in simple recovery then I suspect you are doing it in a transaction. So the 30-40 million deletes are still logged, even in simple recovery because it would all have to be rolled back perhaps.

For 40 x 1 million deletes without a transaction in simpler recovery you can use CHECKPOINT to assist in log tidy up

See Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?) for more

But something like:

SELECT 'Starting' --sets @@ROWCOUNT
WHILE @@ROWCOUNT <> 0
BEGIN
    CHECKPOINT
    --Edit: must be last to set @@ROWCOUNT
    DELETE TOP (1000000) MyTable WHERE ...
END

Process:

• full backup
• change recovery to simple
• delete
• change recovery to full (or what it was before)
• full backup

You don't have many other options if you insist on deleting 30+ million rows in one go in a short windows...

Delete few entries from a table SQL Server

Probably your best bet is to select out the 78 rows you want to keep into a temporary table, then truncate the table and insert them back in.

SELECT * INTO #temp FROM TableName WHERE 

Or if you don't have a specific 78 rows

SELECT TOP 78 * INTO #temp FROM TableName

Then

TRUNCATE TABLE TableName

And last but not least

INSERT INTO TableName
    SELECT * FROM #temp

Doing it this way should be considerably faster depending on what condition you use to get the 78 rows and you avoid bloating the log as TRUNCATE is only minimally logged.

Batch delete data from SQL Server table

Do you mean configuring your sql script as batch job? if yes, then write your sql query in a script like

Filename: sqldelete.sql

delete from my_table where ;
GO

Create a batch file .bat and call your sql script in there like

sqlcmd -s  -d  -i sqldelete.sql

For more options on sqlcmd: run command sqlcmd /? in command prompt

Configure this .bat file to run in schedule as per your need

If you want to know how to schedule task in windows task scheduler then take a look @ http://support.microsoft.com/kb/308569 (This is for WINXP).

Efficient way to delete values based on multiple columns in SQL

You could use a CTE which selects all records that should not be deleted, then you can join it with the original table:

WITH Keep AS
(
    SELECT ID=1234, Sub_ID=2
      UNION ALL
    SELECT ID=4321, Sub_ID=2
)    
SELECT t.* 
FROM Table1 t INNER JOIN Keep k
  ON t.ID = k.ID AND t.Sub_ID = k.Sub_ID 

This shows what you'll keep: demo

If you want to delete the other you can use NOT EXISTS:

WITH Keep AS
(
    SELECT ID=1234, Sub_ID=2
      UNION ALL
    SELECT ID=4321, Sub_ID=2
)   
DELETE t FROM Table1 t WHERE NOT EXISTS
(
   SELECT 1 FROM Keep k 
   WHERE k.ID = t.ID AND k.Sub_ID = t.Sub_ID
)

Demo

This approach should be efficient and readable.

SQL Delete TOP statement

The MSDN DELETE page gives examples and syntax

DELETE TOP (100) FROM Tprs_master where Fincmonth='201109';

Note: SQL Server 2005+ only

Also see for more examples Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)

Edit: OP has SQL Server 2000

SET ROWCOUNT 100
DELETE FROM Tprs_master where Fincmonth='201109';
SET ROWCOUNT 0

---

Related Topics