SQL Server/Table Commands
Introduction SQL Query SQL Stored Procedures SQL Triggers SQL Joins
SQL Views   Indexes BCP (Bulk Copy Program) Table Commands SQL Operators

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)

 

  DROP TABLE:

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.


  DELETE TABLE:

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.

  TRUNCATE TABLE:
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.

  UNLOAD TABLE:

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

  LOAD TABLE:

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