Special characters displaying incorrectly after BULK INSERT

sql sql-server-2012 bulkinsert nvarchar

You need to BULK INSERT using the CODEPAGE = 'ACP', which converts string data from Windows codepage 1252 to SQL Server codepage.

BULK INSERT dbo.temp FROM 'C:\Temp\file.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', CODEPAGE = 'ACP');

If you are bringing in UTF-8 data on a new enough version of SQL Server:

[...] , CODEPAGE = '65001');

You may also need to specify DATAFILETYPE = 'char|native|widechar|widenative'.

CodeHunter