Shulou(Shulou.com)05/31 Report--

How to understand sql 2000 after emptying so that the table id from 1 and other database operations, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

Truncate table table name (data unrecoverable deletion truncate identity column will be rearranged) LinqUtil.Db.ExecuteCommand ("TRUNCATE TABLE warehousing"); / / warehousing is the table name, which is more efficient to delete and will empty the counter, but tables with foreign keys cannot be used. You can use LinqUtil.Db.ExecuteCommand ("delete ioinfo") after deleting the foreign key. / / because this deletion cannot empty the counter, the following empty LinqUtil.Db.ExecuteCommand ("dbcc checkident (ioinfo,reseed,0)"); / / empty the counter, where ioinfo is the table name LinqUtil.Db.SubmitChanges () Execute DBCC CHECKIDENT (table name, reseed, starting value) after emptying the table, such as: you want to DBCC CHECKIDENT (Employee,reseed,0) from 1 after emptying the ID of the table Employee-- you have added a piece of data Add 1 from the starting value-using SQL statements to clear the data of all tables in the database has recently been found to be too large and insufficient space in the database. Therefore, I intend to clean up the data in the database comprehensively, but there are so many tables that it is troublesome to empty them one by one, so I want to use SQL statements to clear all the data at once. Three methods have been found for emptying. The database used is MS SQL SERVER. 1. Search for all the table names and copy the code as a SQL statement: 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 situations where there are not too many tables, otherwise the number of tables is too large, which exceeds the length of the string and cannot be cleaned completely. two。 Use cursors to clean up all tables and copy the 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, can empty all the table data at once, and can also selectively empty the table. 3. Using Microsoft's undisclosed stored procedure exec sp_msforeachtable "truncate table?" This method can empty all tables at once, but can not add filter conditions. -empty table sql statement you can use delete to clear the table DELETE FROM t table name or use the truncate command truncate table 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 tablespace name] [STORAGE (stored clause)] [ENABLE constraint name] [DISABLE constraint name]? Insert data INSERT INTO table name [(column name 1,...)] VALUES (value 1, value 2, … , value n)? Modify data UPDATE table name SET column name 1 = expression 1, column name 2 = expression 2, … WHERE condition;? Delete data deletes data that already exists in the table and cannot delete data that does not exist. Sentence syntax: DELETE FROM table name WHERE condition;? Table structure modification adds new columns to existing tables, sentence syntax: ALTER TABLE table name ADD (new column name data type (length)); for example: ALTER TABLE STUDENT ADD (DEPARTMENT CHAR (8)); b. Increase the data types of existing columns. For example: ALTER TABLE STUDENT MODIFY (NAME VARCHAR2 (25)); Delete a table that already exists. Sentence syntax: DROP TABLE table name; for example: DROP TABLE EMP;? The syntax of the query statement SELECT command is: SELECT [DISTINCT | ALL] {* | pattern name.] {table name | View name | Snapshot name]. *... | {expression [column alias]... }} [, [pattern 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 condition] [UNION | UNION ALL | INTERSECT | MINUS] SELECT command [ORDER BY {expression | location} [ASC | DESC] [, {expression | location [ASC | DESC]}] …]

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.