TRUNCATE TABLE (U-SQL)
U-SQL does not support fine-grained deletion of data with a DELETE statement. Data in a table can be deleted at the level of a vertical partition bucket, or by truncating the whole table with the
TRUNCATE TABLE statement. Assuming the user executing the script has the right permissions, the
TRUNCATE TABLE statement will keep the schema definition of the specified managed table intact and remove all the data contained within the table.
This operation cannot be undone!
Truncate_Table_Statement := 'TRUNCATE' 'TABLE' Identifier [Partition_Label_List].
Identifies the table to be truncated or truncated from. If the Identifier is a three-part identifier, the table from the specified database and schema will be truncated. If the Identifier is a two-part identifier, then the table of the given schema and of the given name of the current static database context will be truncated. If the identifier is a simple identifier, then the table of the given name in the current static database and schema context will be truncated.
If the specified table does not exist, is an external table, or the user has no permissions to delete the data from the table, an error is raised.
The optional partition label list specifies the list of partition buckets to be truncated by specifying the literal values for the partition columns. The values have to be provided as constants or as scalar static variables. For more details on the exact syntax see ALTER TABLE (U-SQL): Adding and Removing Vertical Partition Buckets
- The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
- The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.
The following statement truncates the data in the table
SampleTable in the database
TestReferenceDB’s default schema
TRUNCATE TABLE TestReferenceDB..SampleTable;
The following statement truncates the data in the partition bucket with the value
5 in the vertically partitioned table
SampleTable in the current database and schema context:
DECLARE @p int = 5; TRUNCATE TABLE SampleTable PARTITION (@p);