插入与选择插入 [英] INSERT INTO vs SELECT INTO
问题描述
使用有什么区别
SELECT ... INTO MyTable FROM...
和
INSERT INTO MyTable (...)
SELECT ... FROM ....
?
来自 BOL [INSERT,SELECT...INTO ],我知道使用SELECT...INTO会创建插入表在默认文件组上,如果它不存在,并且此语句的日志记录取决于数据库的恢复模型.
From BOL [ INSERT, SELECT...INTO ], I know that using SELECT...INTO will create the insertion table on the default file group if it doesn't already exist, and that the logging for this statement depends on the recovery model of the database.
- 哪种说法更可取?
- 是否有其他性能影响?
- SELECT...INTO 和 INSERT INTO ... 的最佳用例是什么?
我已经说过我知道 SELECT INTO... 创建了一个不存在的表.我想知道的是 SQL 包含这个语句是有原因的,它是什么?它在插入行的幕后做了什么不同的事情,还是只是在 CREATE TABLE
和 INSERT INTO
之上的语法糖.
I already stated that I know that that SELECT INTO... creates a table where it doesn't exist. What I want to know is that SQL includes this statement for a reason, what is it? Is it doing something different behind the scenes for inserting rows, or is it just syntactic sugar on top of a CREATE TABLE
and INSERT INTO
.
推荐答案
他们做不同的事情.当表存在时使用
INSERT
.如果没有,请使用SELECT INTO
.
They do different things. Use
INSERT
when the table exists. UseSELECT INTO
when it does not.
是的.没有表提示的 INSERT
通常会被记录.SELECT INTO
最少记录,前提是设置了正确的跟踪标志.
Yes. INSERT
with no table hints is normally logged. SELECT INTO
is minimally logged assuming proper trace flags are set.
根据我的经验,SELECT INTO
最常用于中间数据集,例如 #temp
表,或者复制整个表(例如用于备份).INSERT INTO
用于插入到具有已知结构的现有表中.
In my experience SELECT INTO
is most commonly used with intermediate data sets, like #temp
tables, or to copy out an entire table like for a backup. INSERT INTO
is used when you insert into an existing table with a known structure.
编辑
为了解决您的编辑问题,他们会做不同的事情.如果您正在制作表格并想定义结构,请使用 CREATE TABLE
和 INSERT
.可以创建的问题示例:您有一个带有 varchar 字段的小表.现在表中最大的字符串是 12 个字节.您的真实数据集最多需要 200 个字节.如果您从您的小表中执行 SELECT INTO
以创建一个新表,那么后面的 INSERT
将失败并出现截断错误,因为您的字段太小.
To address your edit, they do different things. If you are making a table and want to define the structure use CREATE TABLE
and INSERT
. Example of an issue that can be created: You have a small table with a varchar field. The largest string in your table now is 12 bytes. Your real data set will need up to 200 bytes. If you do SELECT INTO
from your small table to make a new one, the later INSERT
will fail with a truncation error because your fields are too small.
这篇关于插入与选择插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!