Load Data From CSV File into SQL Tables and Views
This example shows how to load data stored in a comma-separated value (CSV) file into an existing table and view.
Create the table to load data into. This table contains three fields specifying membership data: a member ID, the membership term length in months, and the US state where the member lives, using the two-character state abbreviation.
CREATE TABLE Sample.Members (
MemberId INT PRIMARY KEY,
MemberTerm INT DEFAULT 12,
MemberState CHAR(2))
Copy these data records into a text file. Save the file on your local machine and name it members.csv. This file specifies membership IDs and member state values. The second row is missing a value, as indicated by a placeholder comma inserted before where the value would be.
6138830,MA
1720936,
4293608,NH
Use LOAD DATA to load the data into the Sample.Members table. Replace the path shown here with the path where you saved the file.
LOAD DATA FROM FILE 'C://temp/members.csv' INTO Sample.Members (MemberId,MemberState)
Examine the data. The MemberId and MemberState columns have been populated. The source file did not contain data for the MemberTerm column, so these column values default to 12. The missing row value is loaded in as a NULL value.
SELECT * FROM Sample.Members
MemberId |
MemberTerm |
MemberState |
6138830 |
12 |
MA |
1720936 |
12 |
|
4293608 |
12 |
NH |
LOAD DATA does not report an SQLCODE error for the missing data because the overall SQLCODE result of LOAD DATA is 0 (success). A LOAD DATA operation is considered successful if:
-
LOAD DATA can access the source.
-
The target table exists.
-
The LOAD DATA operation is valid. For example, the operation specifies the correct number of columns and the column names exist in the target table.
To view the SQLCODE errors for individual rows, along with other information about the LOAD DATA operation, you can use the %SQL_Diag.Result and %SQL_Diag.Message tables. For more details, see View Diagnostic Logs of Loaded Data.
View the messages from the most recent LOAD DATA operation. The row with the missing state abbreviation reports an SQLCODE error of -104. The results shown are truncated for readability.
SELECT actor,message,severity,sqlcode
FROM %SQL_Diag.Message
WHERE diagResult =
(SELECT TOP 1 resultId
FROM %SQL_Diag.Result
ORDER BY resultId DESC)
actor |
message |
severity |
sqlcode |
server |
{"resultid":"1","bufferrowcount":500, ... } |
info |
0 |
FileReader |
Reader Complete: Total Input file read time: 23 ms, |
completed |
0 |
JdbcWriter |
[SQLCODE: <-104>:<Field validation failed in INSERT>] [%msg: ... (Varchar Value: 'state...' Length: 5) > maxlen: (2)>] |
error |
-104 |
JdbcWriter |
Writer Complete: Total write time: 72 ms, |
completed |
0 |
If you create a view from a table, you can also load data into the table by using the view. Create a view that shows only the membership ID and state columns of the Sample.Members table.
CREATE VIEW Sample.VMem (MId,State) AS SELECT MemberId,MemberState FROM Sample.Members
Copy these additional data records into a text file. Save the file on your local machine and name it members2.csv.
6785674,VT
4564563,RI
4346756,ME
Use LOAD DATA to load this new CSV data into the table by using the view you created.
LOAD DATA FROM FILE 'C://temp/members2.csv' INTO Sample.VMem(MId,State)
View the data returned by the view, which includes the data from both loaded CSV files.
SELECT * FROM Sample.VMem
MId |
State |
6138830 |
MA |
1720936 |
|
4293608 |
NH |
6785674 |
VT |
4564563 |
RI |
4346756 |
ME |
View the data in the base table, which includes combined column data from both CSV files. The default value of 12 is applied to the values in the MemberTerm column for the second loaded CSV file as well.
SELECT * FROM Sample.Members
MemberId |
MemberTerm |
MemberState |
6138830 |
12 |
MA |
1720936 |
12 |
|
4293608 |
12 |
NH |
6785674 |
12 |
VT |
4564563 |
12 |
RI |
4346756 |
12 |
ME |
Delete the view and table.
DROP VIEW Sample.VMem
DROP TABLE Sample.Members