Do NOT run DROP TABLE in PRODUCTION. Use 2 alternate solutions [ETL Scenario -1 ]

Опубликовано: 12 Октябрь 2024
на канале: ETL-SQL

379

In this video I have explained why you should refrain from using DROP-CREATE in PRODUCTION.
Also I have shared 2 alternation approach of fixing the error related with column length.
Queries used in the video:

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender VARCHAR(10),
BirthDate DATE,
Department VARCHAR(10),
Position VARCHAR(50),
Salary INT,
HireDate DATE
);

INSERT INTO Employee VALUES
(1, 'John', 'Doe', 'Male', '1985-05-15', 'IT', 'Software Eng', 75000, '2010-03-20'),
(2, 'Jane', 'Smith', 'Female', '1988-12-10', 'HR', 'HR Manager', 90000, '2012-07-08'),
(3, 'Robert', 'Johnson', 'Male', '1992-08-25', 'Sales', 'Sales Rep', 60000, '2015-01-15'),
(4, 'Emily', 'Davis', 'Female', '1980-06-02', 'Marketing', 'Marketing Mgr', 85000, '2008-11-12'),
(5, 'Michael', 'Brown', 'Male', '1987-04-18', 'Finance', 'Accountant', 70000, '2013-09-30'),
(6, 'Ashley', 'Wilson', 'Female', '1990-09-05', 'IT', 'QA Analyst', 65000, '2016-04-22'),
(7, 'William', 'Taylor', 'Male', '1983-11-30', 'Sales', 'Sales Manager', 80000, '2009-08-14'),
(8, 'Megan', 'Clark', 'Female', '1994-03-22', 'Finance', 'Financial Analyst', 72000, '2017-12-03'),
(9, 'Daniel', 'Miller', 'Male', '1982-07-14', 'IT', 'System Architect', 85000, '2011-05-18'),
(10, 'Olivia', 'Jones', 'Female', '1989-12-01', 'Marketing', 'Marketing Specialist', 67000, '2014-06-28');

-- check loaded rows
SELECT * FROM employee e ;

-- insert new row

INSERT INTO Employee VALUES
(11, 'Mike', 'Myres', 'Male', '1965-05-25', 'Administration', 'Operations Director', 12000, '2024-01-20');

DROP TABLE IF exists Employee ;
CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender VARCHAR(10),
BirthDate DATE,
Department VARCHAR(15),
Position VARCHAR(50),
Salary INT,
HireDate DATE
);
-- This is wrong approach. You are fixing the error but not fixing it in right manner.

-- Following are better approaches
--1)

ALTER TABLE employee alter COLUMN department type varchar(15);

--2)

DROP TABLE IF exists Employee_new;
CREATE TABLE Employee_new (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender VARCHAR(10),
BirthDate DATE,
Department VARCHAR(15),
Position VARCHAR(50),
Salary INT,
HireDate DATE
);

INSERT INTO employee_new SELECT * FROM employee ;

ALTER TABLE employee RENAME to employee_original;
ALTER TABLE employee_new RENAME to employee;

-- optional , after verifying data you can plan to drop original table.
-- to take it to the next level, grant permission to GROUP , USER on the new table to be sure.
--DROP TABLE employee_original;