CSV to SQL Server

Script to Import Data from CSV to SQL Table

CSV File Location:

Traffic census data

Table Structure:

CREATE TABLE dbo.tbl_traffic_census(
    Col_1 int NULL, Col_2 int NULL, Col_3 int NULL,
    Col_4 int NULL, Col_5 int NULL, Col_6 int NULL,
    Col_7 int NULL, Col_8 int NULL, Col_9 int NULL,
    Col_10 int NULL, device_id varchar(255) NOT NULL,
    lane_number varchar(255) NOT NULL,
    cross_datetime datetime NOT NULL,
    speed_kmph_39 int NULL, speed_kmph_40_49 int NULL,
    speed_kmph_50_59 int NULL, speed_kmph_60_69 int NULL,
    speed_kmph_70_79 int NULL, speed_kmph_80_89 int NULL,
    speed_kmph_90_99 int NULL, speed_kmph_100_109 int NULL,
    speed_kmph_110_119 int NULL, speed_kmph_120_129 int NULL,
    speed_kmph_130_more int NULL,
    PRIMARY KEY CLUSTERED 
    (device_id ASC, lane_number ASC, cross_datetime ASC)
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
          ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) 
    ON PRIMARY
);
    

Python Code for Importing Data:

Alternatively, tools like the BCP command, import utility, or SSIS packages can be used for loading data from files into SQL tables.