|
Sorry the information on this page is outdated as it relates to
older versions of SQL Server.
We have new content and free videos at this location,
http://www.learningcomputer.com/videotraining.html Please note that rest of the SQL Server videos can be bought from
this page Buy Tutorials Please send any comments or suggestion to
kashi@learningcomputer.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 |
|
