Did you ever had a CSV file and needed a simple way to import it into SQL Server? If so, try the BULK INSERT command. As long as you do not have any NULL values, you will not find a simpler way.
Your CSV file
Create your CSV file (a file with values separated by commas) without a header or an empty line at the end. It can contain umlaute, but not NULL values and should use the encoding UCS-2 BE BOM:
1 2 3 4 5 6 7 8 9 10 |
1;Gilmoré;Luneä;1956.08.15 2;Lloyd;Evèlyn;1977.08.13 3;Faulkner;Eagan;1948.08.05 4;Fletcher;Chantale;1987.07.23 5;Webster;Yetta;1980.01.13 6;Griffin;Keelie;1939.05.14 7;Mcintosh;Bert;1953.04.07 8;Pope;Brielle;1980.10.13 9;Harmon;Zephania;1984.09.27 10;Elliott;Kirestin;1932.10.27 |
Create the table
The table must be an exact match for the values in your CSV file, including the order of the columns:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE dbo.Authors ( Id [int] IDENTITY(1,1) NOT NULL, LastName [nvarchar](50) NOT NULL, FirstName [nvarchar](50) NOT NULL, BirthDay [date] NOT NULL, CONSTRAINT [PK_dbo_Authors] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] |
The BULK INSERT command
This BULK INSERT command has various options: You can set the character that splits fields, define how lines are separated, select the encoding (via codepage parameter) and define where in the file the import should start:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
BULK INSERT [dbo].Authors FROM 'c:\A\authors.csv' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', CODEPAGE = 'ACP', -- for the umlaute FIRSTROW = 1, -- starts with 1 for the first row --DATAFILETYPE = 'char', KEEPIDENTITY ) GO |
The Result
When the command has finished you will find your data inside your table in SQL Server:
Alternatives when some values are NULL
In this case, the simple command for BULK INSERT is not going to work. A more complex yet more flexible approach is to use format files for the import or the bulk copy program bcp.
Conclusion
For simple files is the BULK INSERT a helpful command that can load your CSV file into SQL Server. Try it before you use a more elaborate approach like format files or write your own tool to load the data.
Good turorial about using BULK INSERT to import from csv files to tables. Thanks
Hi! thanks a lot for your informative article. To learn more about this topic, take a look at this detailed guide describing various options to import CSV files to SQL Server, including ways to automate (I mean schedule) the process and specify FTP or file storages for CSV location https://skyvia.com/blog/3-easy-ways-to-import-csv-file-to-sql-server