Insert Tables - Ecommerce.sql

-- ============================================================
-- BULK INSERT: E-Commerce Database
-- Run these in ORDER to respect foreign key dependencies
-- ============================================================

USE Ecommerce;
GO

-- ============================================================
-- 1) CUSTOMER (no dependencies)
-- ============================================================
BULK INSERT e_commerce.customer
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\customer.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO
/*
-- Step 1: Make sure table is empty before inserting
DELETE FROM e_commerce.customer;
GO

-- Step 2: Run BULK INSERT with error logging
BULK INSERT e_commerce.customer
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\customer.csv'
WITH (
    FIRSTROW        = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\r\n',    -- Windows line ending
    ERRORFILE       = 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\customer_errors.txt',
    TABLOCK
);
GO

-- Step 3: Verify
SELECT COUNT(*) AS RowsInserted FROM e_commerce.customer;
GO
*/

-- ============================================================
-- 2) SELLER (no dependencies)
-- ============================================================
BULK INSERT e_commerce.seller
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\seller.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

--DELETE FROM e_commerce.seller;
--GO

-- ============================================================
-- 3) CATEGORY (no dependencies)
-- ============================================================
BULK INSERT e_commerce.category
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\category.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 4) PRODUCT (depends on: seller, category)
-- ============================================================
BULK INSERT e_commerce.product
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\product.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 5) ADDRESS (depends on: customer)
-- ============================================================
BULK INSERT e_commerce.address
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\address.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 6) CART (depends on: customer)
-- ============================================================
BULK INSERT e_commerce.cart
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\cart.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 7) ORDER_TABLE (depends on: customer, cart)
-- ============================================================
BULK INSERT e_commerce.order_table
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\order_table.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 8) ORDERITEM (depends on: order_table, product)
-- ============================================================
BULK INSERT e_commerce.orderitem
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\orderitem.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 9) PAYMENT (depends on: order_table, customer)
-- ============================================================
BULK INSERT e_commerce.payment
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\payment.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 10) SHIPMENT (depends on: order_table)
-- ============================================================
BULK INSERT e_commerce.shipment
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\shipment.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 11) REVIEW (depends on: customer, product)
-- ============================================================
BULK INSERT e_commerce.review
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\review.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 12) PRODUCT_RETURN (depends on: order_table, product)
-- ============================================================
BULK INSERT e_commerce.product_return
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\product_return.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- 13) SUPPORT_TICKETS (depends on: customer, order_table)
-- ============================================================
BULK INSERT e_commerce.support_tickets
FROM 'F:\Digilians\Courses Online\Data Mining and Visualization\final project\Final work - database Ecommerce\new files 26.3.2026\Tables\support_tickets.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);
GO

-- ============================================================
-- VERIFY: Check all 13 tables loaded correctly
-- ============================================================
SELECT 'customer'        AS TableName, COUNT(*) AS TotalRows FROM e_commerce.customer        UNION ALL
SELECT 'seller'          AS TableName, COUNT(*) AS TotalRows FROM e_commerce.seller          UNION ALL
SELECT 'category'        AS TableName, COUNT(*) AS TotalRows FROM e_commerce.category        UNION ALL
SELECT 'product'         AS TableName, COUNT(*) AS TotalRows FROM e_commerce.product         UNION ALL
SELECT 'address'         AS TableName, COUNT(*) AS TotalRows FROM e_commerce.address         UNION ALL
SELECT 'cart'            AS TableName, COUNT(*) AS TotalRows FROM e_commerce.cart            UNION ALL
SELECT 'order_table'     AS TableName, COUNT(*) AS TotalRows FROM e_commerce.order_table     UNION ALL
SELECT 'orderitem'       AS TableName, COUNT(*) AS TotalRows FROM e_commerce.orderitem       UNION ALL
SELECT 'payment'         AS TableName, COUNT(*) AS TotalRows FROM e_commerce.payment         UNION ALL
SELECT 'shipment'        AS TableName, COUNT(*) AS TotalRows FROM e_commerce.shipment        UNION ALL
SELECT 'review'          AS TableName, COUNT(*) AS TotalRows FROM e_commerce.review          UNION ALL
SELECT 'product_return'  AS TableName, COUNT(*) AS TotalRows FROM e_commerce.product_return  UNION ALL
SELECT 'support_tickets' AS TableName, COUNT(*) AS TotalRows FROM e_commerce.support_tickets
ORDER BY TableName;
GO

-- ============================================================
-- QUICK VIEW: Preview first rows of each table
-- ============================================================
SELECT TOP 5 * FROM e_commerce.customer;
SELECT TOP 5 * FROM e_commerce.seller;
SELECT TOP 5 * FROM e_commerce.category;
SELECT TOP 5 * FROM e_commerce.product;
SELECT TOP 5 * FROM e_commerce.address;
SELECT TOP 5 * FROM e_commerce.cart;
SELECT TOP 5 * FROM e_commerce.order_table;
SELECT TOP 5 * FROM e_commerce.orderitem;
SELECT TOP 5 * FROM e_commerce.payment;
SELECT TOP 5 * FROM e_commerce.shipment;
SELECT TOP 5 * FROM e_commerce.review;
SELECT TOP 5 * FROM e_commerce.product_return;
SELECT TOP 5 * FROM e_commerce.support_tickets;
GO
-- ============================================================
-- VERIFY all tables loaded correctly
-- ============================================================
SELECT 'customer'    AS tbl, COUNT(*) AS rows FROM e_commerce.customer   UNION ALL
SELECT 'seller',              COUNT(*)         FROM e_commerce.seller     UNION ALL
SELECT 'category',            COUNT(*)         FROM e_commerce.category   UNION ALL
SELECT 'product',             COUNT(*)         FROM e_commerce.product    UNION ALL
SELECT 'address',             COUNT(*)         FROM e_commerce.address    UNION ALL
SELECT 'cart',                COUNT(*)         FROM e_commerce.cart       UNION ALL
SELECT 'order_table',         COUNT(*)         FROM e_commerce.order_table UNION ALL
SELECT 'orderitem',           COUNT(*)         FROM e_commerce.orderitem  UNION ALL
SELECT 'review',              COUNT(*)         FROM e_commerce.review     UNION ALL
SELECT 'payment',             COUNT(*)         FROM e_commerce.payment;

select *FROM e_commerce.customer
select *FROM e_commerce.seller
select *FROM e_commerce.address
select *FROM e_commerce.category
select *FROM e_commerce.product
select *FROM e_commerce.cart
select *FROM e_commerce.order_table
select *FROM e_commerce.orderitem
select *FROM e_commerce.review
select *FROM e_commerce.payment


DELETE FROM e_commerce.seller;
--GO

DELETE FROM e_commerce.customer;
DELETE FROM e_commerce.seller;
DELETE FROM e_commerce.category;
DELETE FROM e_commerce.product;
DELETE FROM e_commerce.address;
DELETE FROM e_commerce.cart;
DELETE FROM e_commerce.order_table;
DELETE FROM e_commerce.orderitem;
DELETE FROM e_commerce.payment;
DELETE FROM e_commerce.shipment;
DELETE FROM e_commerce.review;
DELETE FROM e_commerce.product_return;
DELETE FROM e_commerce.support_tickets;