TRUNCATE TABLE name (
Data unrecoverable deletion truncate identity column rearrangement
LinqUtil.Db.ExecuteCommand ("TRUNCATE TABLE warehousing"); Warehousing is a table name, which is more efficient and empties the counter, but a table with foreign keys is not available and can be used after the foreign key is deleted.
LinqUtil.Db.ExecuteCommand ("delete Ioinfo"); Because this type of deletion cannot empty the counter, the following is still empty
LinqUtil.Db.ExecuteCommand ("DBCC CHECKIDENT (ioinfo,reseed,0)"); Empty the counter, ioinfo the table name
LinqUtil.Db.SubmitChanges ();
Execute after emptying the table
DBCC checkident (table name, reseed, starting value)
Such as:
You have to clear the ID of the table Employee and start at 1.
DBCC checkident (employee,reseed,0)--you add a piece of data, starting from the starting value plus 1
-------------------------------------------------------------------------------------
Use SQL statements to empty data for all tables in a database
Recently found that the database is too large, there is not enough space, so it is intended to clean up the data of the database, but the table is very much, a sheet of empty, it is really troublesome, so you want to use the SQL statement to clear all the data at once. Three methods were found for emptying. The database used is MS SQL SERVER.
1. Search out all the table names and construct them into a single SQL statement
Copy Code code as follows:
DECLARE @trun_name varchar (8000)
Set @trun_name = '
Select @trun_name = @trun_name + ' TRUNCATE table ' + [name] + ' from sysobjects where xtype= ' U ' and status > 0
EXEC (@trun_name)
This method is suitable for the table is not very many cases, otherwise the table is too many, more than the length of the string, can not be completely clean.
2. Use cursors to clean up all tables
Copy Code code as follows:
DECLARE @trun_name varchar (50)
DECLARE name_cursor cursor FOR
Select ' TRUNCATE table ' + name from sysobjects where xtype= ' U ' and status > 0
Open Name_cursor
FETCH NEXT from Name_cursor into @trun_name
While @ @FETCH_STATUS = 0
Begin
EXEC (@trun_name)
print ' truncated table ' + @trun_name
FETCH NEXT from Name_cursor into @trun_name
End
Close Name_cursor
Deallocate name_cursor
This is my own construction, can be used as a stored procedure call, to be able to empty all the table data at once, and can also make a selective emptying table.
3. Using Microsoft's undisclosed stored procedures
EXEC sp_msforeachtable "TRUNCATE table?"
This method clears all tables at once, but does not add filter conditions.
-----------------------------------------------------------------------------
Empty table SQL statement
You can use Delete to empty the table
DELETE from T table name
You can also use the TRUNCATE command
TRUNCATE TABLE name
CREATE table table name [table constraint]
(Column name 1 data type [default value 1, column constraint 1]
(Column name 2 data type [default value 2, column constraint 2]
...
Column name n data type [default value n, column constraint n]
[Tablespace table space name]
[STORAGE (clause of storage)]
[ENABLE constraint name]
[DISABLE constraint name]
? Inserting data
INSERT into table name [(column name 1,...)]
Values (value 1, value 2, ..., value N)
? modifying data
UPDATE table Name set column Name 1 = expression 1, column name 2 = Expression 2, ...
WHERE condition;
? Delete data
Deleting existing data in a table cannot delete data that does not exist.
Sentence syntax:
DELETE from table name WHERE condition;
? Modification of table structure
Add a new column to the existing table, sentence syntax:
ALTER table name ADD (new column name data type (length));
For example:
ALTER TABLE STUDENT ADD (DEPARTMENT CHAR (8));
B. Increase the data type of an existing column.
For example:
ALTER TABLE STUDENT MODIFY (NAME VARCHAR2 (25));
? Deletion of the table
Delete the existing table, sentence syntax:
The DROP table table name;
For example:
DROP TABLE EMP;
? Query statement
The syntax for the SELECT command is:
SELECT [distinct| All] {*| mode name.] {table name | view name |
Snapshot name]. *...| {expression [column alias] ...} } [, [Schema name.] {Table name |
View name |} . *...| expression [column Alias]] ...
from [schema name.] {table name | view name | snapshot name} [@ Database chain name] [Table alias]
[, [Schema name.] {table name | view name | snapshot name} [@ Database chain name]
[Table alias]] ...
[Where Condition]
[START with condition CONNECT by condition]
[GROUP by expression [, expression] ... [Having conditions]
[union| UNION All | Intersect| Minus]select command
[Order by{Expression | location} [asc| DESC] [, {expression |-location [asc| DESC]}]
