While bulk copy and other bulk import options are not available on the SQL servers, you can import a CSV formatted file into your database using SQL Server Management Studio.
First, create a table in your database into which you will import the CSV file. After the table is created:
1.Log in to your database using SQL Server Management Studio.
2.Right click the database and select Tasks -> Import Data...
3.Click the Next > button.
4.For Data Source, select Flat File Source. Then use the Browse button to select the CSV file. Spend some time configuring the data import before clicking the Next > button.
5.For Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0). Enter the Server name; check Use SQL Server Authentication, enter the User name, Password, and Database before clicking the Next > button.
6.In the Select Source Tables and Views window, you can Edit Mappings before clicking the Next > button.
7.Check Run immediately and click the Next > button.
8.Click the Finish button to run the package.
--------------------------------------------------------------------
SQL Query for importing CSV File
To handle rows which aren't loaded into table because of invalid data or format, could be handle using ERRORFILE property, specify the error file name, it will write the rows having error to error file. code should look like.
BULK INSERT SchoolsTemp --Table Name
FROM 'C:\CSVData\Schools.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',', --CSV field delimiter
ROWTERMINATOR = '\n', --Use to shift the control to next row
ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
TABLOCK
);