How to Import a CSV in SQL Server

CSV files are a common format for storing and exchanging data. In this tutorial, we’ll explore how to efficiently import CSV files into SQL Server using various methods, including T-SQL, SQL Server Management Studio (SSMS), and SQL Server Integration Services (SSIS).

Method 1: Using SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) offers an easy way to import CSV files into a database using its import wizard. Here's how you can do it:

1. Open SSMS and connect to your SQL Server instance.
2. Right-click on the database where you want to import the CSV file.
3. Select Tasks >Import Data... to launch the Import Data wizard.
4. Choose Flat File Source as your data source and browse to the CSV file.
5. Configure your destination (SQL Server Database) and select the appropriate table or create a new one.
6. Follow the wizard steps to map the columns and finish the import process.

Method 2: Using T-SQL with BULK INSERT

If you prefer a script-based approach, you can use the BULK INSERT command in T-SQL to load data from a CSV file directly into a SQL Server table. Here’s the syntax:

BULK INSERT your_table_name
FROM 'C:\path_to_your_file.csv'
WITH (
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);
                                

In this example:

• FIELDTERMINATOR specifies the delimiter (comma for CSV).
• ROWTERMINATOR indicates the end of each row (new line character).
• FIRSTROW skips the header row in the CSV file.

Method 3: Using SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) is a more powerful tool for managing complex data import tasks. It provides a GUI to create ETL (Extract, Transform, Load) workflows that can handle large data imports with advanced features. To use SSIS, follow these steps:

1. Launch SQL Server Data Tools.
2. Create a new SSIS project.
3. Drag a Data Flow Task into the control flow area.
4. Configure the source to read from your CSV file and the destination to write to your SQL Server database.
5. Run the SSIS package to import the data.

Conclusion

Importing a CSV file into SQL Server is straightforward and can be accomplished using multiple methods depending on your needs and preferences. Whether you're using the SSMS import wizard for simplicity, T-SQL for scripting, or SSIS for more complex workflows, you now have the tools to efficiently load your data into SQL Server.