copying data from one table to another @ mysql

insert into tbl (field1,field2) select field1,field2 from users

Delete vs Truncate @ SQL

SQL Server
TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.

Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster

TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause

Truncate = Delete+Commit -so we cant roll back
Truncate is a Transcation control language

Truncate: Drop all object's statistics and marks like High Water Mark, free extents and leave the object really empty with the first extent.
Delete: You can keep object's statistics and all allocated space.

Truncate will write only one query in the ldf file to remove whole data unlike the Delete query which will write one query per record in the ldf file

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

http://www.geekinterview.com/question_details/425

MYSQL

TRUNCATE TABLE empties a table completely. Logically, this is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

For InnoDB before version 5.0.3, TRUNCATE TABLE is mapped to DELETE, so there is no difference. Starting with MySQL 5.0.3, fast TRUNCATE TABLE is available. However, the operation is still mapped to DELETE if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any AUTO_INCREMENT counter. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset by TRUNCATE TABLE, regardless of whether there is a foreign key constraint.)

For other storage engines, TRUNCATE TABLE differs from DELETE in the following ways in MySQL 5.0:

Truncate operations drop and re-create the table, which is much faster than deleting rows one by one.

Truncate operations are not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.

The number of deleted rows is not returned.

As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted.

The table handler does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

Since truncation of a table does not make any use of DELETE, the TRUNCATE statement does not invoke ON DELETE triggers.

The General Query Log @ Mysql

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed. This logging order contrasts to the binary log, for which statements are written after they are executed but before any locks are released. (Also, the query log contains all statements, whereas the binary log does not contain statements that only select data.)

To enable the general query log, start mysqld with the --log[=file_name] or -l [file_name] option.

If no file_name value is given for --log or -l, the default name is host_name.log in the data directory.

Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). On Unix, you can rename the file and create a new one by using the following commands:
shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> cp host_name-old.log backup-directory
shell> rm host_name-old.log

On Windows, you cannot rename the log file while the server has it open. You must stop the server and rename the file, and then restart the server to create a new log file.