有两种方法可以是实现,而且不使用游标.下面两种方法性能远好于游标.
1.INSERT INTO SELECT
这种方法常用在目标表已经存在数据库中.需要从源表把数据插入到目标表的情况.如果目标表和源表的列相同,就不必列出数据列了.
USE AdventureWorks GO ----Create TestTable CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100)) ----INSERT INTO TestTable using SELECT INSERT INTO TestTable (FirstName, LastName) SELECT FirstName, LastName FROM Person.Contact WHERE EmailPromotion = 2 ----Verify that Data in TestTable SELECT FirstName, LastName FROM TestTable ----Clean Up Database DROP TABLE TestTable GO USE AdventureWorks GO ----Create TestTable CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100)) ----INSERT INTO TestTable using SELECT INSERT INTO TestTable --(FirstName, LastName) SELECT FirstName, LastName FROM Person.Contact WHERE EmailPromotion = 2 ----Verify that Data in TestTable SELECT FirstName, LastName FROM TestTable ----Clean Up Database DROP TABLE TestTable GO
仔细看看有什么区别.
2.SELECT INTO
这种方法用在目标表事先不存在于数据库.在插入数据的同时新建表.新表的字段名和数据类型和需要插入的列相同.
USE AdventureWorks GO ----Create new table and insert into table using SELECT INSERT SELECT FirstName, LastName INTO TestTable FROM Person.Contact WHERE EmailPromotion = 2 ----Verify that Data in TestTable SELECT FirstName, LastName FROM TestTable ----Clean Up Database DROP TABLE TestTable GO
我也常用它来复制数据结构,但不插入数据.
USE AdventureWorks GO ----Create new table and insert into table using SELECT INSERT SELECT FirstName, LastName INTO TestTable FROM Person.Contact WHERE 1=2 DROP TABLE TestTable GO
如果认为此文对您有帮助,别忘了支持一下哦!
作者:齐飞
来源:http://youring2.cnblogs.com/
声明:本博客原创文字只代表本人工作中在某一时间内总结的观点或结论,与本人所在单位没有直接利益关系。非商业,未授权,贴子请以现状保留,转载时必须保留此段声明,且在文章页面明显位置给出原文连接。
转载:http://www.cnblogs.com/youring2/archive/2012/04/06/2434184.html
时间: 2024-09-20 00:22:30