Comparison of INSERT INTO TableName and select * INTO tablename

Select| comparison
Table TableName must exist when insert INTO TableName
Table cannot exist when select * into tablename

The SELECT * into TableName is fast when the database failover model is simple, because select * into tablename does not generate a large number of logs when the database's failover model is "simple"


--Test:
--the prerequisite is that the database's failure-restore model is "simple"

--1, a table with a select into generating a large amount of data you can view your LDF file (log) before the statement runs
-and then look at it after running, log growth is very small, and the table is faster
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (n ' [tb_pwd3] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
drop table [TB_PWD3]
Go
--Generate temporary tables
Select Top 256 seq_no=identity (int,0,1) to #t from syscolumns
--Generate password 3-bit dictionary table contents
Select Pwd=char (a.seq_no) +char (b.seq_no) +char (c.seq_no) into TB_PWD3 from #t A, #t B, #t c
Go

drop table #t

--In both cases, you need to test separately, to test the 2nd situation, you have to ensure that your disk has enough space, the format of the disk should be NTFS format.


--2, insert into a table that generates large amounts of data you can check your LDF file (log) before the statement runs
--and then look at it after the run, log is growing fast, and the table is slow to write log
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (n ' [tb_pwd3] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
drop table [TB_PWD3]
Go
CREATE TABLE TB_PWD3 (
PWD char (3)
)
Go
--Generate temporary tables
Select Top 256 seq_no=identity (int,0,1) to #t from syscolumns
--Generate password 3-bit dictionary table contents
Insert INTO TB_PWD3 Select Pwd=char (a.seq_no) +char (b.seq_no) +char (c.seq_no) from #t A, #t B, #t c
Go

drop table #t

Appendix: Description of the SQL server2000 restore model
SQL Server 2000 provides us with three types of database recovery models: simple (easy recovery), full (complete recovery), bulk_logged (bulk-logged recovery).
The simple recovery model is the easiest to operate, but it is the most inflexible disaster recovery strategy. Choosing a simple recovery model is equivalent to putting the trunc. Log on chkpt. Set to True. Under this recovery model, we can only perform full and differential backups (differential backup): This is because the transaction log is always truncated and the transaction log backup is not available. Generally, for a system that contains critical data, we should not choose a simple recovery model because it does not help us restore the system to the point of failure. When using this recovery model, we can only restore the system to the last successful full and differential backup status. To recover, we first restore the last successful full backup and then restore the differential backup (the differential backup can only apply changes to the database since the last full backup of the database).
The full recovery model puts the trunc. Log on chkpt. Options and select Into/bulk copy options are set to false. Full recovery has the ability to restore a database to a point of failure or to a specific point in time. This model is ideal for protecting environments that contain critical data, but it increases the cost of equipment and management because, if database access is frequent, the system will quickly generate large transaction log records. Because the Select Into/bulk copy is set in this model, False,sql server records all events that include bulk data loading.
The last recovery model is bulk-logged recovery, which trunc. Log on chkpt. Set to False to set select Into/bulk copy to True. In the bulk-logged recovery model, the data loss of bulk copy operations is more severe than the full recovery model. The full recovery model records the full log of bulk copy operations, but under the bulk-logged recovery model, SQL Server records only the minimum logs for these operations and cannot control them individually. In the bulk-logged recovery model, data file corruption may result in the requirement to manually redo the work. The following table compares the characteristics of three types of recovery models. Recovery model benefits will the performance of the work loss be restored to the immediate point?
Simple allows high performance bulk copy operations.
Reclaim log space, making space requirements minimal. Changes that have occurred since the most recent database or differential backup must be redo. Can revert to the end of any backup. You must then redo the changes.
The loss or corruption of a full data file does not result in work losses.
Can revert to any point in time (for example, before an application or user error). Not normally.
If the log is corrupted, you must redo the changes that occurred since the most recent log backup. Can revert to any point in time.
Bulk-Logged logging allows high-performance bulk copy operations.
The bulk operation uses minimal log space. If the log is corrupted, or if a bulk operation has occurred since the most recent log backup, you must redo the changes since the last backup. Otherwise do not lose any work. Can revert to the end of any backup. You must then redo the changes.

In the Options tab of the database, we can choose simple to change the recovery model to a simpler model from the Model Drop-down list box. In addition, Microsoft has extended the ALTER DATABASE command, which we can use to set database properties. For example, use the following T-SQL command to set the recovery model to a full recovery model: ALTER DATABASE Northwind Set RECOVERY