Importing From CSV

We now need to look at importing our csv file into the tables. The stored procedure that handles this is usp_13OrderImport.

We can look to a previous post at this site for the code. It is called T-SQL Bulk Insert-Output of Text Files

After looking at that code, perhaps we can import the header line into a temporary table (in-memory table) and then import the rest of the lines into another temporary (“temp”) table? Then we can add the book supplier to the Suppliers table if they are not already included in that table. Next we can copy the appropriate data from the temporary table to the [Orders] table and to the [Books] table.

ABC Book Supply Inc.,ABC0001,00001,20170401113035
"Microsoft SQL Server 2016: A Beginner's Guide, Sixth Edition",1259641791,Dusan Petovick & nobody else,5,$32.12

Changing the Format of the Incoming File

There are problems with our input file shown above. The one below works. We cannot have a comma in the middle of the name of the book because SQL Server interprets that as a new field because it is specified as a field terminator.

There is a second problem. To fix it we added another column in the Header table [01HeaderBulkInsert] and we added a comma at the end of the first row. When you are using BULK IMPORT you must have the same number of commas (delimiters) in each of the rows. Also, there must be the same number of columns in each of the tables that you are importing to. In this example project, we need five columns in each table.

ABC Book Supply Inc.,ABC0001,00001,20170401113035,
"Microsoft SQL Server 2016: A Beginner's Guide Sixth Edition",1259641791,Dusan Petovick & nobody else,5,$32.12