Please note that the information on
this page is related to an older version of Microsoft SQL
Server.
Click on SQL Server
Tutorials for new training content and material. In
addition we have FREE SQL
Tutorial videos here, so feel free to download
the demos. Our partners also manage two blogs on
the following SQL Server Versions:
-SQL Server 2008
Blog -SQL
Azure Blog
Please send any comments or suggestion
to info@sqlserver2008tutorial.com
DROP TABLE:
DELETE TABLE:
TRUNCATE TABLE:
UNLOAD TABLE: (Sybase only)
LOAD TABLE: (Sybase only)
This SQL command is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table from the database. The syntax is
DROP TABLE [[database.]owner.]table_name
where
database: name of the database owner: owner of the table and NOT the database table_name: Specifies the table to be removed.
An example of this command
Drop table testDB.dbo.patients
Remarks
If you are the table owner, you can drop a table in any database. When you drop a table, rules or defaults on it lose their binding, and any constraints or triggers associated with it are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all necessary constraints. If you delete all rows in a table (DELETE tablename) or use the TRUNCATE TABLE statement, the table still exists until you drop it.
This SQL command is used to delete rows from a database table The syntax for the command is
DELETE [FROM] {table_name | view_name} [WHERE clause]
where
table_name | view_name = [[database_name.]owner.]{table_name | view_name}
Specifies the table or view used in the DELETE statement. If the table or view exists in another database, use a fully qualified table_name or view_name (
WHERE clause = WHERE {search_conditions | CURRENT OF cursor_name}
Is used to perform a searched delete (using search_conditions) or a positioned delete (using CURRENT OF cursor_name). When no WHERE clause is given in the DELETE statement, all rows in the table are removed. The table itself, along with its indexes, constraints, and so on, remains in the database.
Remarks:
The DELETE statement removes rows one at a time and logs each deleted row as a transaction. If your table has a lot of records, you are better off using TRUNCATE TABLE instead.
This SQL command removes all rows from a table without logging the individual row deletes. This allows TRUNCATE TABLE to be efficient and quick, but unrecoverable. The syntax for the command is
TRUNCATE TABLE [[database.]owner.]table_name
where
database: name of the database owner: owner of the table and NOT the database table_name: Specifies the table to be removed.
Remarks
TRUNCATE TABLE removes all rows from a table, but the table structure and all indexes. The TRUNCATE TABLE works similarly to the DELETE statement but is faster than DELETE. The DELETE statement removes rows one at a time and logs each deleted row as a transaction. TRUNCATE TABLE deallocates whole pages of data and makes fewer log entries. TRUNCATE TABLE guarantees that all rows are removed from the table and cannot be rolled back. DELETE can be rolled back. Both the DELETE and TRUNCATE TABLE statements reclaim the space occupied by the data and its associated indexes.
This SQL command is used to export data from a database table into an ascii-format file. The syntax for the command is
UNLOAD TABLE "creator"."table-name" TO 'file-name string' FORMAT 'ascii' DELIMITED BY string
The exact command I used in the project was
UNLOAD TABLE "DBA"."user_table.txt" TO 'F:\\kashef\\user_table.txt' FORMAT 'ascii' DELIMITED BY '~'
Additional Notes
|
• |
For the file-name string make sure you use double back slashes \\ instead of one \ |
|
• |
For the string after DELIMITED BY, you should include single quote ' around the character string |
|
• |
The name of the table creator is optional |
This SQL command is used to import data into a database table from an ascii-format file. The syntax for the command is
LOAD TABLE "creator"."table-name" FROM 'file-name string' FORMAT 'ascii' DELIMITED BY 'string'
The exact command I used in the project was
LOAD TABLE "DBA"."patient" FROM 'F:\\kashef\\patient.txt' FORMAT 'ascii' DELIMITED BY '~'
Additional Notes
|
• |
For the file-name string make sure you use double back slashes \\ instead of one \ |
|
• |
For the string after DELIMITED BY, you should include single quote ' around the character string |
|
• |
The name of the table creator is optional |
|