Pincode Database Schema and Bulk Insert with SQL Server

Pincode_Database_Schema_and_Bulk_Insert.sql

--https://drive.google.com/file/d/1ddUxGN69HDF1F7iGZ4dPlM5ZZluGtiFH/view?usp=sharing

CREATE TABLE Country (
    CountryID INT PRIMARY KEY IDENTITY(1,1),
    CountryName VARCHAR(100) UNIQUE
);

CREATE TABLE State (
    StateID INT PRIMARY KEY IDENTITY(1,1),
    StateName VARCHAR(100) UNIQUE,
    CountryID INT,
    FOREIGN KEY (CountryID) REFERENCES Country(CountryID)
);

CREATE TABLE District (
    DistrictID INT PRIMARY KEY IDENTITY(1,1),
    DistrictName VARCHAR(100),
    StateID INT,
    FOREIGN KEY (StateID) REFERENCES State(StateID)
);

CREATE TABLE DistrictWisePincode (
    PincodeID INT PRIMARY KEY IDENTITY(1,1),
    Pincode VARCHAR(20),
    Area VARCHAR(255),
    DistrictID INT,
    FOREIGN KEY (DistrictID) REFERENCES District(DistrictID)
);


DROP TABLE IF EXISTS TempData;

CREATE TABLE TempData (
    City NVARCHAR(255),
    Area NVARCHAR(500),
    Pincode NVARCHAR(20),
    District NVARCHAR(255),
    State NVARCHAR(255),
    Country NVARCHAR(255) DEFAULT 'India'
);

BULK INSERT TempData
FROM 'C:\Users\Admin\Desktop\India_pincode.csv'
WITH (
    FIELDTERMINATOR = ',',       -- Ensure fields are split correctly
    ROWTERMINATOR = '0x0A',      -- Use this for Unix-style line endings; use '0x0D0A' for Windows
    FIRSTROW = 2,               -- Skip header row
    CODEPAGE = '65001',         -- Ensure UTF-8 encoding
    TABLOCK                    -- Lock table for faster bulk loading
);

INSERT INTO Country (CountryName)
VALUES ('India');

INSERT INTO State (StateName, CountryID)
SELECT DISTINCT State, 1 -- Use 1 for CountryID (India)
FROM TempData;

INSERT INTO District (DistrictName, StateID)
SELECT DISTINCT t.District, s.StateID
FROM TempData t
INNER JOIN State s ON t.State = s.StateName;

INSERT INTO DistrictWisePincode (Pincode, Area, DistrictID)
SELECT DISTINCT t.Pincode, t.Area, d.DistrictID
FROM TempData t
INNER JOIN District d ON t.District = d.DistrictName;