Importing and exporting data from flat files is a common task among developers & DBA. Different methods are available to import-export flat file data in SQL Server. Well-known methods include – BCP, SSIS Packages, OPENROWSET, BULK INSERT queries, and tools. However, the simplest one is doing it within SSMS (SQL Server Management Studio). Using Import Flat File data wizard in SQL Server.
Import Flat File feature is included with SQL Server Management Studio (v 17.3) and later.
This Import File File data wizard simplifies the process compared to former ways. Moreover, with simple UI and fewer configurations plus limited domain knowledge, any user can do this. This wizard is smart and detects the delimiter, column data types using an intelligent framework. With minimal configurations and inputs from the user, – it can import data from text and flat file (CSV) into a new table.
To demonstrate with an example, we are using publicly available sample CSV file data with 13933 rows. We will import Annual enterprise 2019 survey details into dbo.Annual2019SurveyDetails table.
Sample CSV File Data
Import Flat File Data Using Import Export In SQL Server
1.Within SQL Server Management Studio (SSMS), right-click on your target database where flat-file data will be imported.
2.Click on Tasks > Import Flat File.
3.Furthermore, Specify Input File window, browse the CSV file location, and specify the target schema & table name.
4.Preview Data window will show the first 50 sample rows.
5.Modify Column page, here we can make changes to column names which are generated based on file structure, data types, and table constraints, keys.
6.Summary and Result window, you will see the details about the imported file, table, and success & failure results.
Summary
In a nutshell, we have learned a basic and simple way to Import Flat file (CSV) data into SQL Server table. This approach is faster and easy without getting into much configurations. This can work with other delimited files too. Soon Micorosft will be adding more options to this feature. If you like this post you may reach through Tech-Recipes database archive to learn more stuff.
Read more and browse through more posts related to SQL Server on Tech-Recipes.
1. hHow To Use BCP Utility In SQL Server
2. How To Import CSV File Using Bulk Insert In SQL Server
3. SSIS- How To Export & Import ISPAC File SSISDB – Visual Studio
4. How To Create Database Diagram In SQL Server SSMS
5. How To Index Computed Column In SQL Server
6. How To Use Computed Column In SQL Server
7. Execute SQL Files Using SQLCMD
. Import Flat File data SQL Server.){kind=link}