Nhập và Xuất Dữ Liệu – Lab SQL Server 2014 P5

Các dữ liệu trong database thường được nhập trực tiếp bởi các người dùng đang chạy các ứng dụng. Nhưng đôi khi cũng có nhu cầu di chuyển database từ nơi này sang nơi khác. SQL server cung cấp một số công cụ hổ trở xuất nhập dữ liệu như SQL Server Import and Export Wizard, BCP, lệnh BULK INSERT, hàm OPENROWSET hay data-tier applications.

Trong bài lab này hướng dẫn các bạn thực hiện việc di chuyển dữ liệu hay xuất nhập dữ liệu sử dụng các công cụ nêu trên

Thực hiện xong bài lab này, bạn có thể thực hiện nhập xuất dữ liệu trên SQL server 2014 với số lượng lớn từ các nguồn khác nhau

Thực Hiện Nhập và Xuất Dữ Liệu

A: Chuẩn bị:

A: Chuẩn bị: Do bài lab cần sử dụng database có sẳn dữ liệu nên sẽ attach 2 database là Adventureworks.mdf và finance.mdf vào

1. Attach database: vào SSMS phải chuột vào Database chọn Attach Nhập và Xuất Dữ Liệu

Tại cửa sổ Attach Databases ấn nút Add chọn file Adventureworks.mdf (file này có trong folder module 06 hay bạn có thể tìm vào download trên internet) OK

Nhập và Xuất Dữ Liệu

Kiểm tra lại các file .mdf và .ldf OK

Nhập và Xuất Dữ Liệu

Thực hiện lại tương tư cho finance.mdf (file này cũng có trong folder module 06). Hai database đã được attach xong.

Nhập và Xuất Dữ Liệu

2. Connect vào SER100\SQLTEST Instance: Do bài lab cần di chuyển dữ liệu giữa 2 Instance nên sẽ kết nối vào Instance khác là SER100\SQLTEST: Trong cửa sổ Object Explorer chọn connect Database Engine

Nhập và Xuất Dữ Liệu

Tại cửa sổ connect to server, trong phần server name chọn SER100\SQLTEST trong phần Authentication chọn Windows Authentication connect

Nhập và Xuất Dữ Liệu

Kết nối vào SER100\SQLTEST Instance thành công

Nhập và Xuất Dữ Liệu

B. Sử dụng SQL Server Import and Export Wizard:

B. Sử dụng SQL Server Import and Export Wizard:

1. Export dữ liệu từ table Currency ra file currency.csv: Phải chuột vào Adventureworks database chọn Tasks export data, tại màn hình welcome ấn next

Nhập và Xuất Dữ Liệu

Tại màn choose a data source, trong phần data source: chọn SQL server native client 11.0, server name:là tên SQL server, Authentication:chọn Use Windows Authentication (nếu đang logon bằng user sa thì chọn Use SQL Server Authentication) ấn next

picture012

Tại màn hình Choose a Destination, trong phần Destination chọn Flat File Destination, trong phần file name ấn BrowseChọn nơi lưu trữ và đặt tên file là currentcy.csv(trong bài lab này là F:\Export\currency.csv), các tham số còn lại để mặc định ấn next

picture015

Tại màn hình Specify Table Copy or Query chọn option copy data from one or more tables or views next

picture016

Tại màn hình Configure Flat File Destination, trong phần Source table or view chọn [sales].[Currency], Row delimiter chọn {CR}{LF}, Column delimiter:Comma {,} ấn next

picture017

Tại màn hình Save and Run Package chọn option Run immediately next

picture018

Màn hình Complete the Wizard ấn Finish export dữ liệu từ table Currency ra file thành công Close

picture020

Kiểm tra lại file currency.csv đã được export

picture021

2. Export dữ liệu từ table currency ra file excel:

Phải chuột vào Adventureworks database chọn Tasks export data, tại màn hình welcome ấn next. Tại màn choose a data source, trong phần data source: chọn SQL server native client 11.0, server name:là tên SQL server, Authentication:chọn Use Windows Authentication(nếu đang logon bằng user sa thì chọn Use SQL Server Authentication) ấn next

. picture003

Tại màn hình Choose a Destination, trong phần Destination chọn Microsoft Excel, trong phần excel file path ấn browse chọn nơi lưu trữ F:\Export\Currency.xls, trong phần Excel version chọn Microsoft Excel 97-2003, check vào checkbox first row has column name (dòng đầu tiên sẽ là dòng tiêu đề) next

picture004

Tại màn hình Specify Table Copy or Query chọn option copy data from one or more tables or views next

picture005

Tại màn hình Select Source Tables and Views chọn [Sales].[Currency] next

picture006

Tại màn hình Review Data Type Mapping để mặc định next

picture007

Tại màn hình Save and Run Package chọn option Run immediately next

picture008

Màn hình Complete the Wizard ấn Finish export dữ liệu từ table Currency ra file excel thành công Close

picture010

3. Import dữ liệu từ file excel vào table của database DemoDB1: phải chuột vào DemoDB1 database chọn tasks chọn Import Data

picture011

Màn hình welcome ấn next. Tại màn hình Choose a data source, trong phần Data source chọn Microsoft Excel, Excel file path chọn F:\Export\Currency.xls (đã export ở bài lab trên), check vào checkbox first row has column names next

picture014

Tại màn hình Choose a Destination, trong phần Destination chọn SQL Server Native Client 11.0, Server name: tên server cài SQL, chọn option Use windows Authentication(nếu đang logon bằng user sa thì chọn Use SQL Server Authentication), trong phần database chọn DemoDB1 Next.

Tại màn hình Specify Table Copy or Query chọn option copy data from one or more tables or views next

picture015

Tại màn hình Select Source Tables and views chọn ‘Currenct’ next.

Tại màn hình Save and Run Package chọn option Run immediately next Finish

picture017

Import database thành công. Close

picture020

Xem lại trong DemoDB1 database đã có table Currency.

picture021

Phải chuột vào table Currency chọn Edit top 200 rows để xem dữ liệu đã được import vào.

picture022

Chú Ý: Nếu import bị lỗi có thể là do kiểu dữ liệu ngày không phù hợp, bạn vào Control panel chọn date and time change date and time change calendar setting chọn tab Data, chọn định dạng ngày là yyyy-mm-dd, rồi thực hiện import lại

picture029

C. Sử dụng BCP command:

C. Sử dụng BCP command:

1. Format file: Tại SQL server vào run gõ lệnh cmd enter, tại cửa sổ dòng lệnh gõ lệnh bcp -? Enter, để xem các tham số của bcp command

picture022

Tiếp tục gõ lệnh: bcp AdventureWorks.Sales.SelesTaxRate format nul –S ser100 –T –c –t, -r\n –x –f F:\Export\TaxRateFmt.xml (với Ser100 là tên SQL server)

picture023

2. Xuất dữ liệu ra file: gõ lệnh

bcp AdventureWorks.Sales.SelesTaxRate out F:\Export\SalesTaxRate.csv –S ser100

-T –f F:\Export\TaxRateFmt.xml enter.

picture025

D. Dùng Openrowset function:

D. Dùng Openrowset function: Import data từ file vào table, tại SSMS ấn new query nhập lệnh sau

Insert Into Finance.dbo.SalestaxRate

select * from Openrowset (Bulk ‘F:\Export\SalesTaxRate.csv’,

formatfile = ‘F:\Export\TaxRateFmt.xml’) As rows;

picture033

Trong finance database đã có table SalesTaxRate, phải chuột vào table SalesTaxRate chọn edit top 200 rows đễ xem thông tin đã được import.

picture034

E: Sử dụng BULK INSERT:

E: Sử dụng BULK INSERT: Import vào dữ liệu vào table Currency có sẳn, Phải chuột vào table Currency chọn Edit Top 200 Rows, table này chưa có dữ liệu.

Mở New Query thực hiện lệnh sau:

Bulk Insert Finance.dbo.Currency

From ‘F:\Export\Currency.csv’

With

( FIELDTERMINATOR =’,’,

ROWTERMINATOR =’\n’

);

picture028

Phải chuột vào table Currency chọn Edit Top 200 Rows, xem lại nội dung đã insert vào

picture031

F. Data-Tier Applications:

F. Data-Tier Applications: di chuyển dữ liệu qua lại giữa các Instance SQL

1. Export dadabase: Trong Instanse SQL Ser100 phải chuột vào Finance database chọn Tasks chọn Export Data-tier Application, xuất hiện màn hình Introduction ấn Next.

Tại màn hình Export Setting, chọn tab Settings, chọn option Save to local disk ấn Browse, chọn ổ đĩa F:\Export\Finance.bacpac.

picture004

Qua tab Advanced chọn tất cả table next, tại màn hình Summary ấn Finish

picture005

Chờ qua trình export hoàn tất next close

picture009

2. Import dữ liệu vào Instance khác: kết nối vào Ser100\SQLTEST Instance, phải chuột vào Database chọn Import Data-tier Application

picture010

Tại màn hình Introduction ấn Next, Tại màn hình Import Settings chọn option Import From disk ấn Browse chọn file F:\Export\Finance.bacpac next

picture012

Tại màn hình Databases Settings bảo đảm rằng đã kết nối tới SER100\SQLTEST Instance, trong phần New Database Name: Finance, Data file path: F:\Export, Log file path: F:\Export Next, màn hình Summary next

picture013

Chờ Database Import xong Close

Database Finance đã được import thành công

picture018

G. Copy database:

G. Copy database: Trong SER100 Instance phải chuột AdventureWorks database chọn tasks Copy Database. Màn hình Welcome ấn Next. Màn hình Select a source Server chọn SER100 next

picture006

Tại màn hình Select a Destination Server chọn SER100\SQLTEST Next

picture007

Tại màn hình Select Database chọn copy AdventureWorks next

picture008

Tại màn hình Configure Destination Database (1 of 1) trong phần Destination database: AdventureWorks, Kiểm tra nơi lưu trữ của file. Chọn option Stop the transfer if a database or file … next

picture009

Màn hình Select Server Object, kiểm tra xem Logins đã có trong phần Selected related objects chưa next, màn hình Configure the Package next, Màn hình Schedule the Package chọn option Run immediately next

picture010

Màn hình Complete the Wizard finish, hoàn tất việc copy. Kiểm tra xem SER100\SQLTEST Instance có AdventureWorks database chưa.

picture013

Lab SQL Server 2014 phần 6

Mr CTL – labs.ctl.vn

Leave a Reply