SQL Server Import/Export to CSV

There are tools that will help you in importing or exporting CSV files to and from SQL Server databases. However, using them will slow you down for a factor of 20 because of those click, next, next, next wizard thing. Using plain SQL query is much faster especially when you’re on a hurry. This has been on the net but I will write it once again in this post.

Importing CSV file to SQL Server table

To import a CSV file to SQL Server table using just SQL query – you have to do this in SQL Server Management Studio – you have to use the BULK INSERT command.

BULK
INSERT target_table
FROM 'c:\csv_files\for_import.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 1
)
GO

Note that the source table is the on the server. Therefore, if you are connecting remotely via SQL Server Management Studio, copy the CSV file to the server. When you run the query, the data from the CSV file is imported to the target table.

If the first row of the CSV file are column names, you can skip them by specifying FIRSTROW = 2.

Exporting table to CSV File

Exporting to CSV file can also be done using plain SQL query. Once again, the resulting file will be saved to the server. So if you are connecting remotely, get the file from the server after you exported it.

Exporting to CSV uses xp_cmdshell. This must be enabled on the server. Go to SQL Server Surface Area Configuration under Surface Area Configuration for Features and enable xp_cmdshell.

The basic command looks like this:

declare @sql varchar(8000)

select @sql = 'bcp db_name..table_name out c:\csv_files\my_export.csv -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql
This entry was posted in SQL, SQL Server and tagged , , , , , , . Bookmark the permalink.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *