4

Can I use truncate command with a where clause? I need to remove specific rows from several tables.

How can I delete specific data from the entire database?

SELECT DimEmployee.[FirstName], DimEmployee.[LastName], [SalesOrderNumber], [ShipDateKey]
    FROM DimEmployee
    JOIN [FactResellerSales] 
        ON DimEmployee.[EmployeeKey] = [FactResellerSales].[ProductKey] 
    WHERE DimEmployee.[FirstName] like 'kevin%' <--have to truncate this specific name from entire DB

Is there any other method to remove a specific data from entire DB?

In my database there are 172 tables. I wanted to delete a specific name and its corresponding columns from the entire database. The name is spread across entire database, hence I want to remove it in a single shot instead of going to each table and deleting it individually.

10

No, Truncate can't be used with a WHERE clause. Truncate simply deallocates all the pages belonging to a table (or a partition) and its indexes.

From BOL:

-- Syntax for SQL Server and Azure SQL Database  

TRUNCATE TABLE   
    [ { database_name .[ schema_name ] . | schema_name . } ]  
    table_name  
    [ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }   
    [ , ...n ] ) ) ]  
[ ; ]  

If you're looking for a more efficient way to delete data, I'd start here.

4

There are three delete methods in sql server: Truncate , Delete , Drop

DROP, TRUNCATE are DDL commands (DROP is used to remove objects like tables, columns, constraints,...but not rows)

DELETE is a DML commands.

"TRUNCATE Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources..." Read more

You have to create command using a dynamic sql and execute it: (something like this query)

DECLARE @strquery as NVARCHAR(MAX)

SET @strquery = ''
SELECT 'Delete T2 from [' + Table_name + '] As T2
Inner join  DimEmployee as T1 
On T1.[EmployeeKey] = T2.[ProductKey] 
Where    T1.[FirstName] like ''kevin%'';'
 From information_schema.tables
 WHERE table_name <> 'DimEmployee'


EXEC(@strquery)

Helpful links

-1

Truncate is a ddl command which works at page level. Simply deallocate pages at partition and object level. Whereas delete is a dml command and works at row level. Inside the page it searches row offset and marks the record as deleted.

So as per above statement you can not use truncate statement with query.

  • 1
    What does your answer add to the previously posted answers? – mustaccio Jan 11 at 20:16
-2

Can a where Clause be used along with a Truncate command

Every SQL Developer wants full control over the Database by query. Due to which they start thinking beyond the SQL syntax. One such misconception is whether a where Clause be used along with a Truncate command to perform some conditional operations.

For example, if someone wants to delete all records of last week (7 days) using Truncate command. Although this can be done using "Delete from table_name where conditions" but truncate is used because it is faster than Delete query.

The Answer for the question "Can a where Clause be used along with a Truncate command" is YES. Truncate statement means All or Nothing. This means either all the records in the Table will be deleted or none of them will be deleted.

TRUNCATE TABLE

TRUNCATE TABLE is a statement in SQL that deletes all records in a table very fast by deallocating the data pages used by the table. Deallocating the data pages reduces the resource overhead of logging the deletions done, as well as the number of locks acquired. Records removed by the TRUNCATE TABLE query statement cannot be restored, so you need to be very careful before executing TRUNCATE TABLE query.

  • 3
    Don't you mean "No", you can't use a WHERE clause with TRUNCATE? – RDFozz Jan 11 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.