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ị: 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 
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
Kiểm tra lại các file .mdf và .ldf OK
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.
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
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
Kết nối vào SER100\SQLTEST Instance thành công
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
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
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
Tại màn hình Specify Table Copy or Query chọn option copy data from one or more tables or views next
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
Tại màn hình Save and Run Package chọn option Run immediately next
Màn hình Complete the Wizard ấn Finish export dữ liệu từ table Currency ra file thành công Close
Kiểm tra lại file currency.csv đã được export
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
. 
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
Tại màn hình Specify Table Copy or Query chọn option copy data from one or more tables or views next
Tại màn hình Select Source Tables and Views chọn [Sales].[Currency] next
Tại màn hình Review Data Type Mapping để mặc định next
Tại màn hình Save and Run Package chọn option Run immediately next
Màn hình Complete the Wizard ấn Finish export dữ liệu từ table Currency ra file excel thành công Close
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
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
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
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
Import database thành công. Close
Xem lại trong DemoDB1 database đã có table Currency.
Phải chuột vào table Currency chọn Edit top 200 rows để xem dữ liệu đã được import vào.
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
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
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)
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.
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;
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.
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’
);
Phải chuột vào table Currency chọn Edit Top 200 Rows, xem lại nội dung đã insert vào
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.
Qua tab Advanced chọn tất cả table next, tại màn hình Summary ấn Finish
Chờ qua trình export hoàn tất next close
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
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
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
Chờ Database Import xong Close
Database Finance đã được import thành công
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
Tại màn hình Select a Destination Server chọn SER100\SQLTEST Next
Tại màn hình Select Database chọn copy AdventureWorks next
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
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
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.
Mr CTL – labs.ctl.vn