sql server一个容易忽视的“漏洞”------SELECT INTO 语句 潜在问题

SELECT INTO语句常用来给数据表建立备份或是历史档案。

--select * into VMI_MONTHPLAN_HEADER from npm_test.dbo.VMI_MONTHPLAN_HEADER 拷贝表结构和表内容

--select * into VMI_MONTHPLAN_HEADER from npm_test.dbo.VMI_MONTHPLAN_HEADER where 1<>1  只拷贝表结构，不拷贝表内容(空表拷贝)

 

但这个拷贝不包含索引和触发器,同时丢失约束尤其是主键.

 

Sql常用的插入数据的方法除了select into外还有个insert into select 语句

Insert into 有两种方式:

1.    直接数值插入 insert into table1(column1,column2) values(‘value1’,’value2’)

2.    子查询插入 通过查询语句从相关表中读取插入 insert into table2 select column1,column2 from table1

 

Select into 语句主要通过查询语句从相关表中读取数据插入到新创建的表中(新建的表只保留原始结构,但是没什么约束)

 

特殊应用
1. select into 创建和原始列编码一致的表
很多时候数据库，表，和列都有不同的编码，尤其是在一些特殊应用下，
例如英文版的win 2003下装繁体中文版的sql server 2005然后附加简体中文创建的数据库文件，这些时候开发以及维护的时候就会遇到编码问题，结果不能正确显示甚至查询结果不是自己想要的（equal to在不同编码下不能正常工作），解决方法最好的当然是统一所有的编码了，但是如果不能做到的话
这时候一般的解决方法是在要操作数据库下用select into创建临时表，然后将操作数据都放在这个临时表里面，最后在更新到数据库原始表中
虽然步骤上面多了一步，但是可以保证完全可旧数据的编码一致，并且不用硬性转码，这里需要注意的是tempdb这个数据库的编码也不一定和操作数据库的编 码一致，所以临时表一定要创建在操作数据库上
2. select into 创建备份表或者储存查询结果
很多时候如果需要多次查询或者更新后要和原始表做比较的话，就可以很方便的利用select into创建一个备份表或者结果集来存储数据方便进一步的使用及操作

 

不同点
1 .性能
a.如果数据库采用完全日志(日志记录方式:bulk-logged recovery),两者性能并没有大的区别
b.如果简单日志的话，select into会快过insert into很多，测试数据显示select into是insert into大概10倍。由于select into并没有记录下操作日志在ldf文件，而只是简单记录分页日志，所以操作会快很多，但是相应的，这个时候如果试图通过log explorer等日志分析工具来恢复数据库，是行不通的。
2.select into 会创建表，如果目标表已经存在就会报错；insert into需要目标表已经存在，否则也会报错
3.在触发器或者存储过程中使用select into容易造成死循环
4.insert into 可以使用分区表，而select into不能使用，它所创建的表也在数据库默认文件夹下，尽管原始表可能是分区表并具有分区scheme，但是新创建表并不保留这些数据
5.select into不能设定表锁，所以会造成锁表

总结
涉及到编码问题以及测试用途时，可以使用select into以方便创建表，记录数据，保证编码一致。
而在正式开发时候，尽量使用insert into可以保证约束，并尽早发现潜在问题。

 

 

测试/临时备份数据/建立临时表的时候可以用select into，

 编码的问题倒是之前没有用过Select into建立临时表，而是直接Collation转换：

 

select * from hrshhbd

         where hbdwno in (select hbdwno collate Chinese_PRC_CI_AS from forbqy.dbo.hrshhbd where hbdwno = '9907110')