《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》——2.20　使用BULK INSERT实施批量导入

2.20　使用BULK INSERT实施批量导入

本方案描述了如何用PowerShell和BULK INSERT将CSV文件导入SQL Server。

2.20.1　准备

为了做导入测试，我们首先需要创建一个Person表，类似于AdventureWorks2008R2数据库的Person.Person表，简单地修改一下。

我们将在Test架构下创建，并移除一些约束，保持表简单且独立。

为了创建本练习中需要的表，我们打开SSMS，运行如下代码。

CREATE SCHEMA [Test]
GO
CREATE TABLE [Test].[Person](
 [BusinessEntityID] [int] NOT NULL PRIMARY KEY,
 [PersonType] [nchar](2) NOT NULL,
 [NameStyle] [dbo].[NameStyle] NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [dbo].[Name] NOT NULL,
 [MiddleName] [dbo].[Name] NULL,
 [LastName] [dbo].[Name] NOT NULL,
 [Suffix] [nvarchar](10) NULL,
 [EmailPromotion] [int] NOT NULL,
 [AdditionalContactInfo] [xml] NULL,
 [Demographics] [xml] NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL　NOT NULL,
 [ModifiedDate] [datetime] NOT NULL
)
GO

在本方案中，我们将导入AdventureWorks2008R2.Person.Person.csv文件，Packt网站提供了可下载资源。保存在目录C:TempExports下。

或者，创建一个CSV文件，像在使用bcp实施批量导出方案中所提到的，替换文件名。

2.20.2　如何做…

1．通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2．首先添加一些辅助函数。输入如下并执行。

Import-Module SQLPS -DisableNameChecking
function Import-Person {
<#
.SYNOPSIS
　　 Very simple function to get number
　　 of records in Test.Person
.NOTES
　　 Author　　 : Donabel Santos
.LINK
　　 http://www.sqlmusings.com
#>
param([string]$instanceName,[string]$dbName)
$query = @"
TRUNCATE TABLE Test.Person
GO
BULK INSERT AdventureWorks2008R2.Test.Person
　　FROM 'C:\Temp\Exports\AdventureWorks2008R2.Person.Person.csv'
　　WITH
　　　 (
　　　　　 FIELDTERMINATOR ='|',
　　　　　 ROWTERMINATOR ='\n'
　　　 )
SELECT COUNT(*) AS NumRecords
FROM AdventureWorks2008R2.Test.Person
"@;
#check number of records
Invoke-Sqlcmd -Query $query `
-ServerInstance "$instanceName" `
-Database $dbName
}

3．现在在同一个会话中调用该函数。

$instanceName = "KERRIGAN"
$dbName = "AdventureWorks2008R2"
Import-Person $instanceName $dbName

2.20.3　如何实现…

使用BULK INSERT命令，从CSV或文本文件中将记录导入到SQL Server表，需要创建BULK INSERT T-SQL语句，并使用Invoke-Sqlcmd执行以下语句。

Invoke-Sqlcmd -Query $query `
-ServerInstance "$instanceName" `
-Database $dbName

然而，我们的做法不同于之前的方案。在本方案中，我们首先创建了一个函数，将所有核心导入任务括起来。

创建函数时，我们首先需要创建函数头部。

function Import-Person {

函数头部以function开始，然后跟随着函数名，以动词-名词的形式。函数体通过一对大括号括起来。

在函数头部后面，创建了注释信息，对头部进行注释。

<#
.SYNOPSIS
　　 Very simple function to get number　　　of records in Test.Person
.NOTES
　　 Author　　 : Donabel Santos
.LINK
　　 http://www.sqlmusings.com
#>

在PowerShell中，块注释以<#开始，以#>结束。此外，这是一个特别类型的块注释，可以在Get-Help中显示函数的注释。现在我们输入：

Get-Help Import-Person

你得到的结果与从其他的cmdlet中获得的帮助类似。

在函数头部和注释后面是参数。Import-Person函数接受两个参数：instance name和database name。

param([string]$instanceName,[string]$dbName)

参数定义之后是函数定义。我们创建一个字符串，保存T-SQL语句。

$query = @"
TRUNCATE TABLE Test.Person
GO
BULK INSERT AdventureWorks2008R2.Test.Person
　　FROM 'C:\Temp\Exports\AdventureWorks2008R2.Person.Person.csv'
　　WITH
　　　 (
　　　　　 FIELDTERMINATOR ='|',
　　　　　 ROWTERMINATOR ='\n'
　　　 )
SELECT COUNT(*) AS NumRecords
FROM AdventureWorks2008R2.Test.Person
"@;

在创建查询后，我们将其传递给Invoke-Sqlcmd cmdlet，让它在SQL Server实例中执行。

Invoke-Sqlcmd -Query $query `
-ServerInstance "$instanceName" `
-Database $dbName

在PowerShell中，函数默认是本地域范围，但是当通过ISE运行时，将保持一个全局域范围。在我们的方案中，一旦你运行第一部分含有函数定义的脚本，你可以在当前会话中的任何时候调用函数。我们可以看到该函数简化了导入记录，我们只需要明确实例名、数据库名和Import-Person函数。

$instanceName = "KERRIGAN"
$dbName = "AdventureWorks2008R2"
Import-Person $instanceName $dbName

如果你使用Shell，想要函数能在全局域范围内访问，保存该脚本为.ps1文件，并点号加载它。另一个方法是添加函数名和global。

function global:Import-Person {

2.20.4　请参阅…

执行查询语句/SQL脚本方案
使用bcp实施批量导入。