SQL SERVER 如何向表中插入数据.doc

相信大家都知道怎样使用数据操作语言(DML)对SQL Server表的数据进行插入、删除和更新等处理。有时候，我们需要用INSERT语句进行插入的数据实在是多得让人头疼，有很多传统但繁琐的方法可以用来插入大批量数据，不过SQL Server 2008提供了一种能够简化数据插入过程的新方法。本文将为大家简单介绍这些用来插入数据的方法之间的差异，其中包括SQL Server 2008提供的新方法行值构造器(Row Value Constructor)。我们向表插入数据的传统方法有三个，介绍如下：方法一假设我们有一个名为MyTestDB的数据库，其中有一个名为MyTest1的表，数据库和表的创建过程如下：USE master GO IF EXISTS (SELECT name FROM sys.databases WHERE name = NMyTestDB) DROP DATABASE MyTestDB GO Create database MyTestDB Go Use MyTestDB Go IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Ndbo.MyTest1) AND type in (NU) DROP TABLE dbo.MyTest1 GO USE MyTestDB GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE dbo.MyTest1( Id int NULL, Fname varchar(100) NULL, Lname varchar(100) NULL, salary money NULL ) ON PRIMARY GO SET ANSI_PADDING OFF GO 现在我们用传统的ANSI插入语句向上表添加5行数据，这里需要用到带VALUE从句的INSERT SQL语句来插入数据，脚本如下：insert into MyTest1 (id ,fname ,lname , salary) values (1 , John , Smith , 150000.00)insert into MyTest1 (id ,fname ,lname , salary) values (2 , Hillary , Swank , 250000.00)insert into MyTest1 (id ,fname ,lname , salary) values (3 , Elisa , Smith , 120000.00)insert into MyTest1 (id ,fname ,lname , salary) values (4 , Liz , Carleno , 151000.00)insert into MyTest1 (id ,fname ,lname , salary) values (5 , Tony , Mcnamara , 150300.00)执行结果如下：(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)方法二假设我们在上述的MyTestDB数据库中有表MyTest2，如下：USE MyTestDB GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Ndbo.MyTest2)AND type in (NU) DROP TABLE dbo.MyTest2 GO CREATE TABLE dbo.MyTest2( Id int NULL, Fname varchar(100) NULL, Lname varchar(100) NULL, salary money NULL ) ON PRIMARY GO SET ANSI_PADDING OFF GO 下面我们再用另外一种传统的插入方法同样添加5行数据，也就是使用带SELECT从句的INSERT SQL语句，脚本如下：insert into MyTest2 select 1 , John , Smith , 150000.00insert into MyTest2 select 2 , Hillary , Swank , 250000.00insert into MyTest2 select 3 , Elisa , Smith , 120000.00insert into MyTest2 select 4 , Liz , Carleno , 151000.00insert into MyTest2 select 5 , Tony , Mcnamara , 150300.00执行结果如下：(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)方法三同样的，我们再假设上述的MyTestDB数据库中有表MyTest3，如下：USE MyTestDB GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Ndbo.MyTest3)AND type in (NU) DROP TABLE dbo.MyTest3 GO USE MyTestDB GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE dbo.MyTest3( Id int NULL, Fname varchar(100) NULL, Lname varchar(100) NULL, salary money NULL ) ON PRIMARY GO SET ANSI_PADDING OFF GO 下面我们用第三种传统的插入方法同样添加5行数据，这里使用的是带SELECT从句和UNION从句的INSERT SQL语句，脚本如下：insert into MyTest3select 1 , John , Smith , 150000.00union select 2 , Hillary , Swank , 250000.00union select 3 , Elisa , Smith , 120000.00union select 4 , Liz , Carleno , 151000.00union select 5 , Tony , Mcnamara , 150300.00执行结果如下：(5 row(s) affected)方法四最后一种方法，需要插入数据的对象是MyTestDB数据库中的表MyTest4，如下：USE MyTestDB GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(Ndbo.MyTest4)AND type in (NU) DROP TABLE dbo.MyTest4 GO USE MyTestDB GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE dbo.MyTest4( Id int NULL, Fname varchar(100) NULL, Lname varchar(100) NULL, salary money NULL ) ON PRIMARY GO SET ANSI_PADDING OFF GO 现在我们要用到SQL Server 2008中提供的新方法行值构造器的插入SQL语句为上述表插入5行数据，这种方法可以在一个INSERT语句中一次性插入多行数据，脚本如下：insert into MyTest4 (id ,fname ,lname , salary) values(1