Monday, December 10, 2012

SELECT INTO vs INSERT SELECT

In the situation of moving data around in the database, there are two common methods usually adapted in the circle.

Method 1: CREATE and INSERT

CREATE TABLE targetTbl (
xxx
xxx
xxx
);

INSERT INTO targetTbl (xxx)
SELECT xxx
FROM source
WHERE xxx;

Method 2: SELECT INTO

SELECT XXX
INTO targetTbl
FROM source
WHERE xxx;

Which way is better? It depends on the recovery mode set on the database. For both SIMPLE and BULK-LOGGED mode, since SELECT INTO is a bulk operation, it logs minimal information, so that SELECT INTO is more efficient than INSERT method.

What about the comparison under FULL recovery mode? They are about the same.

No comments: