SQL 2017: Bulk Insert, now with Quotes!
Author: jason
Date: 2019-05-04 08:17:58
Category: Technical

So... SQL just got the ability to import quoted fields as of SQL 2017. Where have you been all my life? Seriously... It's the little things.

So what that means is, if you have a CSV like "One","Two" now SQL will import the data between the quotes, and not include the quotes. This is Huge. Everybody thinks so...

Code

BULK INSERT dbo.ExampleTableForImport FROM 'C:TempImportMeIgnoringQuotes.csv'
WITH (FIRSTROW = 2 --Start with Row 2, as Row 1 is our Column Headers
, FIELDTERMINATOR = ',' --Split on the Comma
, ROWTERMINATOR = 'n' --How do we know what is the next line? '0x0A' Can be used for Unix/Oracle generated files
, FORMAT = 'CSV' --We are a CSV file
, FIELDQUOTE = '"' --Our data is wrapped in a Quote
, KEEPIDENTITY --If importing Primary Keys
, MAXERRORS = 0 --Stop on any error
, TABLOCK --Lock the Table
);




https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017





jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009