Home / Tổng hợp / cách import file .sql vào sql server

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

admin 13/11/2022

Import Flat tệp tin Wizard is a simple way to lớn copy data from a flat file (.csv, .txt) khổng lồ a new table in your database. The Import Flat file Wizard supports both comma-separated and fixed width format files. This overview describes the reasons for using this wizard, how khổng lồ find this wizard, & a simple example khổng lồ follow.

Why would I use this wizard?

This wizard was created khổng lồ improve the current import experience leveraging an intelligent framework known as Program Synthesis using Examples (PROSE). For a user without specialized domain knowledge, importing data can often be a complex, error prone, and tedious task. This wizard streamlines the import process as simple as selecting an input đầu vào file and unique table name, & the PROSE framework handles the rest.

PROSE analyzes data patterns in your input đầu vào file lớn infer column names, types, delimiters, and more. This framework rmeilan.com.vns the structure of the file and does all of the hard work so users don"t have to.

To further understand the user experience improvement of the Import Flat file Wizard, check out this video:

Prerequisites

This feature is available on SQL vps Management Studio (SSMS) v17.3 or later. Make sure you are using the latest version. You can find the latest version here.

Getting Started

To access the Import Flat tệp tin Wizard, follow these steps:

Open SQL server Management Studio.Connect lớn an instance of the SQL server Database Engine or localhost.Expand Databases, right-click a database (test in the example below), point to Tasks, and click Import Flat File above Import Data.

To rmeilan.com.vn more about the different functions of the wizard, refer lớn the following tutorial:

Tutorial

For the purposes of this tutorial, feel không tính phí to use your own flat file. Otherwise, this tutorial is using the following CSV from Excel, which you are free to copy. If you use this CSV, title it example.csv and make sure khổng lồ save it as a csv in an easy location such as your desktop.

Overview:

Step 1: Access Wizard và Intro Page

Access the wizard as described here.

The first page of the wizard is the welcome page. If you vày not want lớn see this page again, feel không lấy phí to click Do not show this starting page again.

Xem thêm: " Em Đã Làm Gì Có Người Yêu, Em Còn Đang Sợ Ế Đây Này? Em Đã Làm Gì Có Người Yêu, Em Đang Sợ Ế Đây Này

Step 2: Specify input File

Click browse to lớn select your đầu vào file. At default, the wizard searches for .csv and .txt files. PROSE will detect if the file is comma-separated or fixed-width format regardless of tệp tin extension.

The new table name should be unique, & the wizard does not allow you to lớn move further if not.

Step 3: Preview Data

The wizard generates a preview that you can view for the first 50 rows. If there are any problems, click cancel, otherwise proceed to lớn the next page.

Step 4: Modify Columns

The wizard identifies what it believes are the correct column names, data types, etc. Here is where you can edit the fields if they are incorrect (for example, data type should be a float instead of an int).

Columns where empty values are detected will have "Allow Nulls" checked. However if you expect nulls in a column và "Allow Nulls" is not checked, here is where you can update the table definition lớn allow nulls in one or all columns.

Proceed when ready.

Step 5: Summary

This is simply a summary page displaying your current configuration. If there are issues, you can go back khổng lồ previous sections. Otherwise, clicking finish attempts the import process.

Step 6: Results

This page indicates whether the import was successful. If a green kiểm tra mark appears, it was a success, otherwise you may need to reviews your configuration or input đầu vào file for any errors.

Troubleshooting

The Import Flat tệp tin Wizard detects the data types based on the first 200 rows. In scenarios where data further in the flat file does not conform to the automatically detected data types, an error will occur during import. The error message would be similar khổng lồ the following:

Error inserting data into table. (rmeilan.com.vn.SqlServer.Prose.Import)The given value of type String from the data source cannot be converted to lớn type nvarchar of the specified target column. (System.Data)String or binary data would be truncated. (System.Data)Tactics khổng lồ alleviate this error:

Error inserting data into table occured while inserting rows 1 - 2. (rmeilan.com.vn.SqlServer.Prose.Import)The given value of type String from the data source cannot be converted to type float of the specified target column. (System.Data)Failed to convert parameter value from a String khổng lồ a Double. (System.Data)