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