SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE
来源:互联网 发布:php 分割二维数组 编辑:程序博客网 时间:2024/05/11 11:36
Following three questions are many time asked on this blog.
How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?
There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.
Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.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
Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.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
Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer articleSQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.
- SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE
- SQL Server Insert data into table
- Mysql select data from one table into a new one
- SELECT INTO FROM和INSERT INTO SELECT ,CREATE TABLE 表名(SELECT)
- insert into table from another table without duplicate
- Insert Data into Sorted Table
- Insert Data into Sorted Table
- sqlserver——insert into /select into/create table/drop
- sql server 2008 copy data from one table to another
- SQL SERVER Insert into Select
- SELECT INTO , INSERT INTO SELECT 和 CREATE TABLE AS SELECT 的区别
- SELECT INTO , INSERT INTO SELECT 和 CREATE TABLE AS SELECT 性能对比
- 另一种insert into table()select... 语句的用法
- convert the Data from Table into Insert Sentence.
- insert into select from
- insert into select from
- create table as select 和 create table 再insert into select 的差异
- oracle数据迁移--复制表--create table as select & select into & insert into
- 汉诺塔问题C++的递归实现 [
- 发一个迷宫问题(又称电路板问题)的源码
- 索引选定步骤[摘]
- wxPython Installation
- goole搜索引擎的工作流程
- SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE
- 缺點問答
- 随机数
- [转]每天读一遍,不久你就会变!
- DBNull与Null的区别
- 閒暇時寫的一個基於SOCKET的文件傳輸程序
- ssh 速度慢的问题
- os/commsfw/commsconfig/commsdatabaseshim/commdbshim/Notifier
- 我寫的串口同步通信類------適合初學者