setup-data.sql

-- Delete database if already exists. This enables us to run without errors
drop database  if exists ConsumerComplaint
go

-- create the ConsumerComplaint database
create database ConsumerComplaint
GO

-- use just created database
Use ConsumerComplaint;

-- Delete table if already exists. This enables rerun this script without errors
DROP TABLE IF EXISTS ConsumerComplaint.dbo.Consumer_Complaints;
GO

-- Create Consumer_Complaints table which we will be uploading dataset into
CREATE TABLE Consumer_Complaints (
    date_received varchar(12),
    product_name varchar(500),
    sub_product varchar(500),
    issue varchar(5000),
    sub_issue varchar(5000),
    consumer_complaint_narrative varchar(5000),
    company_public_response varchar(5000),
    company varchar(500),
    state_name varchar(2),
    zip_code varchar(6),
    tags varchar(400),
    consumer_consent_provided varchar(30),
    submitted_via varchar(100),
    date_sent varchar(12),
    company_response_to_consumer varchar(5000),
    timely_response varchar(30),
    consumer_disputed varchar(30),
    complaint_id varchar(10)
)
GO

-- Upload data to created table
BULK INSERT Consumer_Complaints
FROM '/home/sql/dataset/ConsumerComplaints.csv'
WITH (
    FORMAT = 'CSV',         --file format
    FIRSTROW = 2,           --start at second row.
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)
GO