Cách import file .sql vào sql server

In this article

Applies to:

*
SQL Server (all supported versions)
*
Azure Squốc lộ Database

There are several ways to lớn import data from Excel files to lớn Squốc lộ Server or khổng lồ Azure Squốc lộ Database. Some methods let you import data in a single step directly from Excel files; other methods require you to lớn export your Excel data as text (CSV file) before you can import it. This article summarizes the frequently used methods & provides link for more detailed information.

Bạn đang xem: Cách import file .sql vào sql server

List of methods

You can use the following tools to lớn import data from Excel:

Export lớn text first (Squốc lộ Server and SQL Database)Directly from Excel (SQL Server on-premises only)
Import Flat File WizardSQL Server Import & Export Wizard
BULK INSERT statementSQL Server Integration Services (SSIS)
BCPOPENROWSET function
Copy Wizard (Azure Data Factory)
Azure Data Factory
  

If you want lớn import multiple worksheets from an Excel workbook, you typically have sầu khổng lồ run any of these tools once for each sheet.

A complete description of complex tools & services lượt thích SSIS or Azure Data Factory is beyond the scope of this list. To learn more about the solution that interests you, follow the provided liên kết.


Important

For detailed info about connecting lớn Excel files, & about limitations & known issues for loading data from or khổng lồ Excel files, see Load data from or khổng lồ Excel with Squốc lộ Server Integration Services (SSIS).


If you don"t have sầu Squốc lộ Server installed, or you have Squốc lộ Server but don"t have Squốc lộ Server Management Studio installed, see Download Squốc lộ Server Management Studio (SSMS).

Squốc lộ Server Import & Export Wizard

Import data directly from Excel files by stepping through the pages of the SQL Server Import & Export Wizard. Optionally, save the settings as a SQL Server Integration Services (SSIS) package that you can customize và reuse later.

In Squốc lộ Server Management Studio, connect khổng lồ an instance of the Squốc lộ Server Database Engine.

Exp& Databases.

Right-clichồng a database.

Point khổng lồ Tasks.

Click one of the following options.

Import Data

Export Data

*

*

For an example of using the wizard khổng lồ import from Excel to SQL Server, see Get started with this simple example of the Import and Export Wizard.

To learn about other ways to launch the Import & Export wizard, see Start the Squốc lộ Server Import and Export Wizard.

SQL Server Integration Services (SSIS)

If you"re familiar with SSIS & don"t want khổng lồ run the SQL Server Import và Export Wizard, create an SSIS package that uses the Excel Source and the Squốc lộ Server Destination in the data flow.

For more info about these SSIS components, see the following topics:

To start learning how to lớn build SSIS packages, see the tutorial How khổng lồ Create an ETL Package.

*

OPENROWSET và linked servers


Important

In Azure Squốc lộ Database, you cannot import directly from Excel. You must first export the data to a text (CSV) tệp tin. For examples, see Example.


Note

The ACE provider (formerly the Jet provider) that connects lớn Excel data sources is intended for interactive client-side use. If you use the ACE provider on Squốc lộ Server, especially in automated processes or processes running in parallel, you may see unexpected results.


Distributed queries

Import data directly inlớn SQL Server from Excel files by using the Transact-Squốc lộ OPENROWSET or OPENDATASOURCE function. This usage is called a distributed query.


Important

In Azure Squốc lộ Database, you cannot import directly from Excel. You must first export the data to lớn a demo (CSV) file. For examples, see Example.


Before you can run a distributed query, you have sầu khổng lồ enable the ad hoc distributed queries hệ thống configuration option, as shown in the following example. For more info, see ad hoc distributed queries Server Configuration Option.

sp_configure "show advanced options", 1;RECONFIGURE;GOsp_configure "Ad Hoc Distributed Queries", 1;RECONFIGURE;GOThe following code sample uses OPENROWSET lớn import the data from the Excel Sheet1 worksheet inkhổng lồ a new database table.

USE ImportFromExcel;GOSELECT * INTO Data_dqFROM OPENROWSET("wpuonline.com.ACE.OLEDB.12.0", "Excel 12.0; Database=C:TempData.xlsx", );GOHere"s the same example with OPENDATASOURCE.

USE ImportFromExcel;GOSELECT * INTO Data_dqFROM OPENDATASOURCE("wpuonline.com.ACE.OLEDB.12.0", "Data Source=C:TempData.xlsx;Extended Properties=Excel 12.0")...;GOTo append the imported data lớn an existing table instead of creating a new table, use the INSERT INTO ... SELECT ... FROM ... syntax instead of the SELECT ... INTO ... FROM ... syntax used in the preceding examples.

To query the Excel data without importing it, just use the standard SELECT ... FROM ... syntax.

For more info about distributed queries, see the following topics:

Linked servers

You can also configure a persistent connection from SQL Server lớn the Excel tệp tin as a linked server. The following example imports the data from the Data worksheet on the existing Excel linked VPS EXCELLINK into a new SQL Server database table named Data_ls.

USE ImportFromExcel;GOSELECT * INTO Data_ls FROM EXCELLINK...

Xem thêm: Cách Chơi Game Siêu Nhân Anh Hùng Trái Đất 2 Người Đánh Nhau

;GOYou can create a linked hệ thống from Squốc lộ Server Management Studio, or by running the system stored procedure sp_addlinkedserver, as shown in the following example.

DECLARE
RC intDECLARE
hệ thống nvarchar(128)DECLARE
srvsản phẩm nvarchar(128)DECLARE
provider nvarchar(128)DECLARE
datasrc nvarchar(4000)DECLARE
location nvarchar(4000)DECLARE
provstr nvarchar(4000)DECLARE
catalog nvarchar(128)-- Set parameter valuesSET
server = "EXCELLINK"SET
srvsản phẩm = "Excel"SET
provider = "wpuonline.com.ACE.OLEDB.12.0"SET
datasrc = "C:TempData.xlsx"SET
provstr = "Excel 12.0"EXEC
RC = ..
VPS,
srvhàng hóa,
provider,
datasrc,
location,
provstr,
catalogFor more info about linked servers, see the following topics:

For more examples & info about both linked servers & distributed queries, see the following topics:

Prerequisite - Save sầu Excel data as text

To use the rest of the methods described on this page - the BULK INSERT statement, the BCP.. tool, or Azure Data Factory - first you have sầu to lớn export your Excel data lớn a text file.

In Excel, select File | Save As and then select Text (Tab-delimited) (*.txt) or CSV (Comma-delimited) (*.csv) as the destination tệp tin type.

If you want to lớn export multiple worksheets from the workbook, select each sheet & then repeat this procedure. The Save as command exports only the active sầu sheet.


Tip

For best results with data importing tools, save sầu sheets that contain only the column headers & the rows of data. If the saved data contains page titles, blank lines, notes, and so forth, you may see unexpected results later when you import the data.


The Import Flat File Wizard

Import data saved as text files by stepping through the pages of the Import Flat File Wizard.

As described previously in the Prerequisite section, you have sầu to lớn export your Excel data as text before you can use the Import Flat File Wizard khổng lồ import it.

For more info about the Import Flat File Wizard, see Import Flat File to lớn SQL Wizard.

BULK INSERT command

BULK INSERT is a Transact-Squốc lộ command that you can run from SQL Server Management Studio. The following example loads the data from the Data.csv comma-delimited file into an existing database table.

As described previously in the Prerequisite section, you have sầu to lớn export your Excel data as text before you can use BULK INSERT lớn import it. BULK INSERT can"t read Excel files directly. With the BULK INSERT commvà, you can import a CSV file that is stored locally or in Azure Blob storage.

USE ImportFromExcel;GOBULK INSERT Data_bi FROM "C:Tempdata.csv" WITH ( FIELDTERMINATOR = ",", ROWTERMINATOR = " ");GOFor more info và examples for SQL Server & Squốc lộ Database, see the following topics:

BCP tool

BCPhường is a program that you run from the commvà prompt. The following example loads the data from the Data.csv comma-delimited file into the existing Data_bcp database table.

As described previously in the Prerequisite section, you have to export your Excel data as text before you can use BCPhường. to lớn import it. BCPhường. can"t read Excel files directly. Use to import into lớn SQL Server or Squốc lộ Database from a test (CSV) tệp tin saved to local storage.


Important

For a text (CSV) tệp tin stored in Azure Blob storage, use BULK INSERT or OPENROWSET. For an examples, see Example.


bcp.exe ImportFromExcel..Data_bcp in "C:Tempdata.csv" -T -c -t ,For more info about BCPhường., see the following topics:

Copy Wizard (Azure Data Factory)

Import data saved as text files by stepping through the pages of the Azure Data Factory Copy Wizard.

As described previously in the Prerequisite section, you have to lớn export your Excel data as text before you can use Azure Data Factory lớn import it. Data Factory can"t read Excel files directly.

For more info about the Copy Wizard, see the following topics:

Azure Data Factory

If you"re familiar with Azure Data Factory và don"t want to run the Copy Wizard, create a pipeline with a Copy activity that copies from the text tệp tin to SQL Server or lớn Azure SQL Database.

As described previously in the Prerequisite section, you have sầu to lớn export your Excel data as text before you can use Azure Data Factory to import it. Data Factory can"t read Excel files directly.

For more info about using these Data Factory sources & sinks, see the following topics:

To start learning how lớn copy data with Azure data factory, see the following topics:

Comtháng errors

wpuonline.com.ACE.OLEDB.12.0" has not been registered

This error occurs because the OLEDB provider is not installed. Install it from wpuonline.com Access Database Engine 2010 Redistributable. Be sure khổng lồ install the 64-bit version if Windows và Squốc lộ Server are both 64-bit.

The full error is:

Msg 7403, Level 16, State 1, Line 3The OLE DB provider "wpuonline.com.ACE.OLEDB.12.0" has not been registered.

Cannot create an instance of OLE DB provider "wpuonline.com.ACE.OLEDB.12.0" for linked server "(null)"

This indicates that the wpuonline.com OLEDB has not been configured properly. Run the following Transact-Squốc lộ code lớn resolve this:

EXEC sp_MSset_oledb_prop N"wpuonline.com.ACE.OLEDB.12.0", N"AllowInProcess", 1EXEC sp_MSset_oledb_prop N"wpuonline.com.ACE.OLEDB.12.0", N"DynamicParameters", 1The full error is:

Msg 7302, Level 16, State 1, Line 3Cannot create an instance of OLE DB provider "wpuonline.com.ACE.OLEDB.12.0" for linked VPS "(null)".

The 32-bit OLE DB provider "wpuonline.com.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit Squốc lộ Server

This occurs when a 32-bit version of the OLD DB provider is installed with a 64-bit SQL Server. To resolve sầu this issue, uninstall the 32-bit version and install the 64-bit version of the OLE DB provider instead.

The full error is:

Msg 7438, Level 16, State 1, Line 3The 32-bit OLE DB provider "wpuonline.com.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit Squốc lộ Server.

The OLE DB provider "wpuonline.com.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give sầu any information about the error

Cannot initialize the data source object of OLE DB provider "wpuonline.com.ACE.OLEDB.12.0" for linked VPS "(null)"

Both of these errors typically indicate a permissions issue between the Squốc lộ Server process and the file. Ensure that the tài khoản that is running the SQL Server service has full access permission lớn the file. We recommend against trying to import files from the desktop.

Xem thêm: Cách Xóa Khoảng Trắng Cuối Trang Trong Word 2010, Mẹo Xóa Khoảng Trắng Cuối Trang Trong Word

The full errors are:

Msg 7399, Level 16, State 1, Line 3The OLE DB provider "wpuonline.com.ACE.OLEDB.12.0" for linked VPS "(null)" reported an error. The provider did not give any information about the error.Msg 7303, Level 16, State 1, Line 3Cannot initialize the data source object of OLE DB provider "wpuonline.com.ACE.OLEDB.12.0" for linked server "(null)".

See Also

Import data from Excel or export data lớn Excel with Squốc lộ Server Integration Services (SSIS)


Chuyên mục: Hosting