Microsoft® SQL Server® 2012 Bible

797

Chapter 30: Confi guring and Managing SQL Server with PowerShell

30

$ScriptDrop.Script($ScriptThis)

$scriptr.Script($ScriptThis)

} #This ends the loop

} #This completes the function

The best method for creating and modifying tables in SQL Server is to use Transact-SQL

scripts.

After scripting out the database objects, as shown in Listing 30-6, at some point you likely

need to load them into a database. One of the easiest ways to do that is by simply pointing

the Get-ChildItem cmdlet at the directory that contains the fi les for the table defi nition,

and then streaming that list of fi les to the -InputFile parameter of the

Invoke-SQLCmd cmdlet (see Listing 30-7).

LISTING 30-7 CreateTable.ps1

dir -recurse -Path C:\TEMP\Databases\AdventureWorksLT\201203041924\Tables |

where{ Test-Path $_.fullname -pathtype leaf} |

%{

Invoke-Sqlcmd -ServerInstance LocalHost -Database BlankDB -InputFile "$($_.FullName)"

}

Data-Based Tasks

In the section on ADO.NET, you saw an example that returned the results from a query

to the user, using a Invoke-SQLCmd cmdlet to fi ll a DataTable by passing the results

through the Out-DataTable function. This method is fi ne as long as there’s enough mem-

ory to hold all the results of the query. If the result set is large, though, it is better to use

the ExecuteReader method of the SqlCommand object.

The following example, as shown in Listing 30-8, uses the AdventureWorks2012 data-

base to extract department employee information by DepartmentID and creates a

separate physical fi le for each department. The fi les are text fi les with commas sepa-

rating the columns returned. This format is easily understood by most programs

that import data. The ExecuteReader method returns a DataReader object, and

the columns must be retrieved from the object using the GetValue method of the

DataReader, supplying the column index number to GetValue. The script sets a local

variable for each of the columns retrieved for each row. After those have been set, the

script tests whether the DepartmentID value has changed. If so, a “header” row is

created in a string variable ($r) and written to a fi le with the name of the department

Name value and a .txt extension. After the header is written, the data row just read is

written to the fi le.

c30.indd 797c30.indd 797 7/31/2012 9:46:24 AM7/31/2012 9:46:24 AM

http://www.it-ebooks.info