Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Module Contents:

Lesson 1: Lesson 2: Lesson 3: Lab:

Module While a great by users who

entered directly in other

locations to and SQL Server provides tools, such as database engine. function, are data-tier applications (

Some of these external to the OPENROWSET create objects

associated with In this module,

and export

data to and from

Objectives

1 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

After completing • Describe tools • Import and • Copy or move

Lesson The first step the processes

familiar with data

transfer. When large amounts for constraints, You may achieve

default settings performance possible. made by

constraints are

Lesson After completing • Describe core • Describe the • Improve the • Disable and • Disable and

Overview

2 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

Not all data can

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

imported

from external

often request

that data from

have seen

how collations

a database

also often requires Data Transfer Although not

process that most

data transfer • Extracting • Transforming • Loading the

Together, these

Load (ETL)

process, which

Note: appropriate transformations before.

3 of 42

be more data rather than

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Extracting While there are

on a source

system to retrieve During the extraction • To avoid excessive data when the same data,

entire tables of continually re-read source system

in any way. • To ensure

one row from

the source

Transforming The transformation

such as the

following: • Data might

erroneous data or

provide default • Lookups might

include the name

of a customer, • Data might transaction

every daily summary

values. • Data might example,

allocation. For might need daily

budgets.

In addition to example by pivoting columns into

4 of 42

some way, for multiple source columns.

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Loading Data After data is

target system.

Instead of performing

special options

for loading data

changes to

improve the

Available

SQL Server provides

important to

understand which SQL Server The SQL Server

Server

Integration Services (

Server. SSIS is

capable of connecting

perform

complex transformations

out of the

box and can

and scripts.

Bulk Copy You can use

from an

operating system

Server table

to an operating

5 of 42

Transact-SQL

26/09/2016 15:47

Module 6: Importing and Exporting Data

queryout option,

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

does not

require any knowledge BULK INSERT You can use operating system

from an differs from

bcp in a number

within

Transact-SQL,

bcp utility

can be used for

be used for

data import. OPENROWSET ( OPENROWSET

retrieve data

from OLE-DB

need to be

provided as parameters

OPENROWSET to

connect to other SQL Server

with the

OPENROWSET The BULK provider

Improving

6 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

If you enable transfers, SQL

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

data constant

checking can Disabling Constraints, Rather than checking

index for every

row, you can

or index

updating until

end of the

import process. For example, does exist whenever reference for resulting in thousands

customer could check this thousands of orders, you can

check the customer completes—to Only CHECK

disabling and

re-enabling constraints

7 of 42

Similar to the

data import

can improve

same effect. In

many cases,

faster than 26/09/2016 15:47

Module 6: Importing and Exporting Data

updating the

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

when there is

a much larger Triggers are the processing

decide if after the

import, rather Control Locking By default, SQL

the execution

of commands.

when a

significant number

locks

occupies resources

queries. As the

data in tables

accessed by the

process that this reason, it

present. For query hint

during the import Use Minimal Minimal logging improvements—for

faster,

minimal logging Not all commands indicate the types

items below logging:

• The table • Table locking • If the table always minimally

data pages are whether the

table is empty. • If the table

8 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

• If the table • If the table

minimally

logged. • If a table has

are both fully

logged, regardless

Disabling

Prior to SQL the data in the to put the index

updated when when you need configured.

Disabling Indexes In SQL Server index details

dropping the place and

just stops it from

disabled indexes.

You can disable

Management Studio

(SSMS) or by The following dbo.Customer

9 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Disabling an

ALTER INDEX DISABLE;

You can also

example:

Disabling All

ALTER INDEX DISABLE;

Note: disabled,

The major advantage back into operation to know details

index is

put the index do not need administrative

scripts that stop

are taking

place, and that

completed.

Rebuilding After data has tools in SSMS

graphical DBCC

DBREINDEX The following dbo.Customer Rebuilding an

10 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

ALTER INDEX REBUILD;

You can also

all indexes

on a specified If a large volume

index,

dropping existing

existing one, you

can use the CREATE

shown in the

following example. Recreating an

CREATE INDEX WITH (DROP_EXISTING

Disabling

You can use important constraints key constraints

11 of 42

Two constraints. Primary table and

26/09/2016 15:47

Module 6: Importing and Exporting Data

unique constraints

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

values. SQL

Server creates Disabling Primary To disable a associated with constraints. When

that is primary key automatically

rebuilt. If duplicate

constraint will

fail. For this

need to be sure

that the data

Note: associated

disabling the index

Foreign Key You use foreign entities in another can be entered. the references. points to, the the primary key

referred to by purchase order while checking key reference you re-enable constraints are not

automatically You can use

column or the

relationship between You can disable

CHECK and

NOCHECK Disabling Foreign

ALTER TABLE

12 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

ALTER TABLE

You can also

with the ALL

keyword.

Lesson SQL Server provides lesson, you will

data. In this export data to

and from a SQL

Lesson After completing • Use the SQL • Use the bcp • Use the BULK • Use the OPENROWSET

The SQL

13 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

You can use source for which available. For

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

any data provider is Microsoft Office

Excel®, and

Note: Export 32-bit Import/Export installation

You can use

Import and only have version of the option during Wizard.

also save the

SSIS package Running SSIS SSIS provides • DTExec utility

You can use

specify

parameters including

variables, and

input parameters.

14 of 42

package,

26/09/2016 15:47

Module 6: Importing and Exporting Data

configures the

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

package. It

returns an exit • DtExecUI

The Execute Management command prompt

Server DTExec the utility

and receiving You can also

to automate

and schedule

can configure

the parameters

Note:

The Import and provides a comprehensive

SSIS), which Export

Wizard itself

name, the

data type, and

files, the SQL

Server Import

you need to

develop a more (SSDT-BI) add-in

Tools for BI SSIS

packages.

Note: 20463D:

course

Demonstration: In this demonstration, • Use Import

15 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Demonstration Use Import 1.

Ensure and log

are running, password

Pa$$w0rd 2.

In the D:

3.

Start SQL

database engine

using Windows 4.

In Object

AdventureWorks database,

point to 5.

On the

Next.

6.

On the

select SQL

Server

selected, that Use

Windows

database is

selected; 7.

On the Destination

select Flat File \Demofiles\Mod06\Currency.csv, clear

the Column 8.

On the

one or more

tables 9.

On the

list, select

[Sales].

the Column

delimiter 10. On the

selected, and

click Next 11.

On the

is

successful,

16 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

12. Start Excel

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

folder and view

the data

The bcp

You can use SQL Server numbers of new

of Microsoft import large data files.

bcp Syntax The syntax for

options. The

general form • A table or • A direction (

from SQL

Server) • A local file

You can also database based

exporting).

from the the following

commonly used

17 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

• -Sserver\instance • -d: The database

fully-qualified table

or view name AdventureWorks. • -T: Specifies

Windows

authentication. • -U user_name • -P password • -c: Specifies • -w: Specifies • -n: Specifies • -fformat_file • -t delimiter • -r delimiter

default is a tab. default is a new

line.

Note: -?

command bcp

The following

using Windows

authentication,

AdventureWorks

database to a

comma-delimited

character format Using bcp to

18 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

bcp AdventureWorks.

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

MIA-SQL

-T -c -t ,

Using Format While you can

SQL Server

native format,

formats for

the data you format information, delimiter for

specifying any prefix length, and save the

specified schema

Format files

define the schema

defined as text or

XML files. To preemptively

name of the

format file you

prefix length,

and delimiter

schema in the

format file. The

to create an

XML format

specific field and

row terminators, The following

format file

named CurrencyFmt. Creating a Format

bcp AdventureWorks.

-c -t

, -r \n

To use a format The following Finance.dbo.

19 of 42

the –f parameter

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

specifies the Importing Data

bcp Finance.

-f

D:\CurrencyFmt.

Demonstration: In this demonstration, • Use bcp to • Use bcp to

Demonstration Use bcp to Create 1.

Ensure

2.

Open a

syntax help:

bcp

3.

In the command bcp -T -c

4.

Start Notepad

format file: MIA-SQL xml

folder. Then view

the XML

20 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Use bcp to Export 1.

In the command

data from SQL

Server: bcp

\Demofiles\Mod06

\SalesTaxRate. \TaxRateFmt.

2.

Close the

3.

Start Excel view the

folder and file.

The BULK

The BULK INSERT similar to that by the SQL Server

functionality is data file is read statement executes

within a Transact-SQL

process, data

is not copied

the bcp utility

runs in a separate

on the same

system.

21 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

A key considerations must be accessible the correct drive running the BULK client computer,

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

source data must use example, when sqlcmd from a server, not

the client. Constraints The BULK INSERT options that you utility, the default FOREIGN KEY

FIRE_TRIGGERS Unlike the bcp CHECK and import

operations. Also unlike bcp,

user-defined

transaction, which

operations in a single

transaction. Care

batches that you

import within

might occur,

even when the In the following

table from a

text file on the Using the BULK

BULK INSERT FROM WITH (

); GO

22 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Demonstration: In this demonstration, • Use the BULK

Demonstration Use the BULK 1.

Ensure

2.

In SQL

expand

Finance

click Select

Top 1000 3.

View the

empty.

4.

Click New

Transact-SQL code:

BULK FROM WITH (

);

5.

Click Execute

6.

Switch and click

Currency table populated

with the

The OPENROWSET

23 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

You can use you use Transact-SQL

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

provider, enabling sources.

SQL Server

you to access

data in files.

can also be

used with this The following

into the

dbo.Accounts

schema of the

rows in the text Using OPENROWSET

INSERT INTO SELECT * FORMATFILE = 'D:\AccountsFmt.

Note: correlation

Similarly to

24 of 42

have a

OPENROWSET function

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

refer to volumes Two key advantages with a WHERE

in a query in a SELECT

statement that Inserting BLOB In addition to enable it to import

options that single

column of a • SINGLE_CLOB.

as a single

value of data • SINGLE_NCLOB.

file as a single

value of data • SINGLE_BLOB.

data type

varbinary(

In the following

the

Document column Inserting Data

INSERT INTO SELECT 2013 * SINGLE_BLOB)

Note: distributed DisallowAdhocAccess to 0. This

25 of 42

the ad hoc explicitly set

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers \MSDASQL. When ad hoc access external OLE-DB

Demonstration: In this demonstration, • Use the OPENROWSET

Demonstration Use the OPENROWSET 1.

Ensure

2.

In SQL dbo.SalesTaxRate

3.

View the

4.

Click New

1000 Rows. currently empty. Transact-SQL code:

INSERT SELECT \SalesTaxRate. FORMATFILE

5.

Click Execute

6.

Switch table and

rows;

SalesTaxRate now

populated

26 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Lesson The techniques

data to and

from individual

entire database

from one SQL

Lesson After completing • Describe options • Use the Copy • Export and

Options

There are several

Server. These

include: • Detach and

files as

required, and

27 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

• Backup and

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

different SQL

Server instance. • The Copy

database

and its dependencies • Data-Tier

and import it

on a different

The detach/attach database is copied,

However, only the as logins,

certificates, and

The Copy

The Copy Database database with

copy the possible to

schedule the The wizard provides use detach and

source database

needs to be offline

uses the

SQL Server

the objects

and transfer

28 of 42

configured to

kept online

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

while copying. If you select select copy, the database, moving

afterwards. If you detaching the database

back to an instance

Note: database,

Running the

copy a

instances and a

network connection

Demonstration: In this demonstration, • Use the Copy

Demonstration Use the Copy 1.

Ensure

2.

In SQL

drop-down list

click Database

using Windows

authentication. 3.

In Object

and verify

that the 4.

In Object

AdventureWorks

database, 5.

29 of 42

On the

26/09/2016 15:47

Module 6: Importing and Exporting Data

6.

On the

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

the Use

Windows 7.

On the MIA-SQL\SQL2

8.

On the

Then click Next. attach

method 9.

On the

AdventureWorks

database 10. On the

settings for the

database 11.

On the

Selected related

objects 12. On the

logging

options. 13. On the

selected, and

click Next 14. On the 15. Wait for 16. In Object click Refresh

Databases and copied to this

instance.

Data-Tier

30 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

Data-Tier applications ( deployment,

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

development, instance-level

dependencies.

deployment.

Installation and

line of

business applications.

numbers of

simpler applications.

Note: XML reason, Server that are

example, For this SQL the objects

Creating a DACs provide

applications, as

occurs with Windows

Studio, and

includes all required

can be

installed. For

only be

installed on SQL When the DAC its dependent

31 of 42

database schema and deployment. A

26/09/2016 15:47

Module 6: Importing and Exporting Data

single .dacpac

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

portable across

different environments Extracting Database Administrators (

existing database.

This enables

also makes

it possible to

dependencies

for deployment Alternatively, that also includes phases. First,

DAC package performed in two then the data is

bulk exported Deploying To deploy a data-tier SQL Server deployment. instance along data-tier application

provided in automate the SQL Server versioned defined on

the target instance If you have previously import it into

package, you can server along

with its data. Data-Tier Application One of the benefits be upgraded

versioned and can large

organizations

sites or

virtual servers.

application is

installed in each

Demonstration: 32 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Application In this demonstration, • Export a data-tier • Import a data-tier

Demonstration Export a Data-Tier 1.

Ensure

2.

In SQL the Finance careful

right-click Application. (Be Application.)

3.

On the

4.

On the

enter the

path D:

and verify

that all 5.

On the

6.

Wait for

Import a Data-Tier 1.

In SQL instance,

2.

On the

3.

On the

SQL\SQL2 Application.

selected and enter

the path

33 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

4.

On the

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

name and

file paths, 5.

On the

6.

Wait for

7.

On Object

refresh the

Database

Lab: Importing Scenario You are a DBA InternetSales

HumanResources, import and export

data to and from

Objectives After completing • Use the SQL • Use the bcp • Use BULK • Use OPENROWSET

Lab Setup Estimated Time: Virtual machine: User name: ADVENTUREWORKS

34 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Password: Pa$$w0rd In the following Document column

the SignedAccounts.pdf

file is inserted

Exercise

Wizard

Scenario The production summarize historical

to plan future

production. You have created returns the required

confirmed that it the data in a

Microsoft Excel The main tasks 1.

Prepare

2.

Use the

Task 1: Prepare

1.

Ensure then log

running, and password

Pa$$w0rd 2.

Run Setup.

Task 2: Use

1.

Use the

from the

InternetSales

35 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

o

Export

o

Include

o

Use

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

\Labfiles\Lab06\Starter folder. \Labfiles\Lab06

\Starter

Result: After workbook named

an Excel

Exercise

Scenario Adventure Works periodically data to the Human has asked you

agency and sends the HR manager the

HumanResources The main tasks 1.

Create

2.

Use bcp

Task 1: Create

1.

Use SQL

JobCandidate table in the

HumanResources

o

2.

36 of 42

Note

Use the

26/09/2016 15:47

Module 6: Importing and Exporting Data

o

Ensure

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

terminator and a

new o

Save

\Labfiles\Lab06\Starter folder.

Task 2: Use

1.

Use Notepad

\Labfiles\Lab06\Starter

folder. Note 2.

Use the

JobCandidates

table in

o

3.

Use

Use SQL HumanResources

Result: After JobCandidateFmt. HumanResources

JobCandidate table in the imported.

into the

Exercise

Scenario Adventure Works

e-ecommerce

application developers

A comma-

delimited file

volume used

by database server,

table in the

InternetSales The main tasks 1.

37 of 42

Disable

26/09/2016 15:47

Module 6: Importing and Exporting Data

2.

Use the

3.

Rebuild

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Task 1: Disable

1.

Use SQL

CurrencyRate table in the

InternetSales 2.

In Object

3.

Disable

table.

Task 2: Use

1.

Use Excel

note that it

contains 2.

In SQL Server

statement to import

the data InternetSales 3.

Verify that

Task 3: Rebuild

1.

Rebuild

Result: After data into the

to load

Exercise

Scenario The recruitment

to import

only records

38 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

The main tasks 1.

Copy Data

2.

Disable

3.

Use the

4.

Re-Enable

Task 1: Copy

1.

Use Notepad note that

2.

Copy the

folder and email addresses. \Labfiles\Lab06

\Starter

Note: In this would need to from the server.

environment you is accessible accessible from the

server. Task 2: Disable

1.

Use SQL

JobCandidate table in the

HumanResources 2.

In Object

JobCandidate

table. 3.

Disable

4.

Disable

Task 3: Use

1.

In SQL Server Transact-SQL

INSERT the

dbo.JobCandidate

39 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

2.

o

Use

o

Specify

o

Use

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

null.

Verify that

Task 4: Re-Enable

1.

Rebuild

2.

Re-enable

Result: After the dbo.JobCandidates

JobCandidates2.txt into

Review Review Question(

Module In this module,

move or copy a

database between When planning

Best Practice: • Choose • Use • Use • Use

inserted.

• Try

40 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

Review Question(

41 of 42

26/09/2016 15:47

Module 6: Importing and Exporting Data

42 of 42

https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

26/09/2016 15:47